జాబితాతో పవర్ క్వెరీలో బల్క్ టెక్స్ట్ రీప్లేస్‌మెంట్. అక్యుములేట్ ఫంక్షన్

సూత్రాలతో సూచన జాబితా ప్రకారం వచనాన్ని త్వరగా మరియు పెద్దమొత్తంలో ఎలా భర్తీ చేయాలి - మేము ఇప్పటికే దాన్ని క్రమబద్ధీకరించాము. ఇప్పుడు పవర్ క్వెరీలో దీన్ని చేయడానికి ప్రయత్నిద్దాం.

తరచుగా జరుగుతుంది నిర్వహించడానికి ఈ పని వివరించడం కంటే చాలా సులభం ఎందుకు ఇది పని చేస్తుంది, అయితే రెండింటినీ చేయడానికి ప్రయత్నిద్దాం 🙂

కాబట్టి, మేము కీబోర్డ్ సత్వరమార్గంతో సాధారణ పరిధుల నుండి సృష్టించబడిన రెండు "స్మార్ట్" డైనమిక్ పట్టికలను కలిగి ఉన్నాము Ctrl+T లేదా జట్టు హోమ్ - టేబుల్‌గా ఫార్మాట్ చేయండి (హోమ్ — టేబుల్ లాగా ఫార్మాట్ చేయండి):

నేను మొదటి టేబుల్‌ని పిలిచాను సమాచారం, రెండవ పట్టిక - డైరెక్టరీఫీల్డ్ ఉపయోగించి పట్టిక పేరు (టేబుల్ పేరు) టాబ్ నమూనా రచయిత (రూపకల్పన).

టాస్క్: పట్టికలోని చిరునామాలను భర్తీ చేయండి సమాచారం నిలువు వరుస నుండి అన్ని సంఘటనలు కనుగొనేందుకు హ్యాండ్బుక్ కాలమ్ నుండి వారి సంబంధిత సరైన ప్రతిరూపాలకు సబ్స్టిట్యూట్. సెల్‌లలోని మిగిలిన వచనం తాకబడకుండా ఉండాలి.

దశ 1. డైరెక్టరీని పవర్ క్వెరీలోకి లోడ్ చేసి, దానిని జాబితాగా మార్చండి

యాక్టివ్ సెల్‌ను రిఫరెన్స్ టేబుల్‌లోని ఏదైనా ప్రదేశానికి సెట్ చేసిన తర్వాత, ట్యాబ్‌పై క్లిక్ చేయండి సమాచారం (తేదీ)లేదా ట్యాబ్‌లో శక్తి ప్రశ్న (మీకు Excel పాత వెర్షన్ ఉంటే మరియు మీరు పవర్ క్వెరీని ప్రత్యేక ట్యాబ్‌లో యాడ్-ఇన్‌గా ఇన్‌స్టాల్ చేసినట్లయితే) బటన్‌పై పట్టిక/పరిధి నుండి (టేబుల్/పరిధి నుండి).

రిఫరెన్స్ టేబుల్ పవర్ క్వెరీ క్వెరీ ఎడిటర్‌లో లోడ్ చేయబడుతుంది:

జోక్యం చేసుకోకుండా ఉండటానికి, స్వయంచాలకంగా జోడించిన దశ సవరించిన రకం (మార్చబడిన రకం) కుడి ప్యానెల్‌లో, దరఖాస్తు చేసిన దశలు సురక్షితంగా తొలగించబడతాయి, దశను మాత్రమే వదిలివేయవచ్చు మూల (మూలం):

ఇప్పుడు, మరిన్ని రూపాంతరాలు మరియు భర్తీలను నిర్వహించడానికి, మేము ఈ పట్టికను జాబితా (జాబితా)గా మార్చాలి.

లిరికల్ డైగ్రెషన్

• టేబుల్ అనేక అడ్డు వరుసలు మరియు నిలువు వరుసలతో కూడిన ద్విమితీయ శ్రేణి.
• రికార్డ్ (రికార్డ్) - ఒక డైమెన్షనల్ అర్రే-స్ట్రింగ్, పేర్లతో అనేక ఫీల్డ్‌లు-ఎలిమెంట్‌లను కలిగి ఉంటుంది, ఉదాహరణకు [పేరు = “మాషా”, లింగం = “f”, వయస్సు = 25]
  
• <span style="font-family: Mandali; "> జాబితా</span> - ఒక డైమెన్షనల్ శ్రేణి-నిలువు వరుస, ఉదాహరణకు అనేక మూలకాలను కలిగి ఉంటుంది {1, 2, 3, 10, 42} or { "నమ్మకము ఆశ ప్రేమ" }
  

మా సమస్యను పరిష్కరించడానికి, మేము ప్రధానంగా రకంపై ఆసక్తి కలిగి ఉంటాము <span style="font-family: Mandali; "> జాబితా</span>.

ఇక్కడ ట్రిక్ ఏమిటంటే పవర్ క్వెరీలోని జాబితా అంశాలు సామాన్య సంఖ్యలు లేదా వచనం మాత్రమే కాకుండా ఇతర జాబితాలు లేదా రికార్డ్‌లు కూడా కావచ్చు. ఇది అటువంటి గమ్మత్తైన జాబితాలో (జాబితా), రికార్డులు (రికార్డులు) కలిగి ఉంటుంది, మనం మా డైరెక్టరీని తిప్పాలి. పవర్ క్వెరీ సింటాక్టిక్ సంజ్ఞామానంలో (చదరపు బ్రాకెట్లలోని ఎంట్రీలు, కర్లీ బ్రాకెట్లలోని జాబితాలు) ఇది ఇలా కనిపిస్తుంది:

{

    [ కనుగొను = “సెయింట్. పీటర్స్‌బర్గ్", రీప్లేస్ = "సెయింట్. పీటర్స్‌బర్గ్"] ,

    [ కనుగొను = “సెయింట్. పీటర్స్‌బర్గ్", రీప్లేస్ = "సెయింట్. పీటర్స్‌బర్గ్"] ,

    [ వెతుకు = “పీటర్”, రీప్లేస్ = “సెయింట్. పీటర్స్‌బర్గ్"] ,

మొదలైనవి

}

పవర్ క్వెరీలో నిర్మించిన M భాష యొక్క ప్రత్యేక ఫంక్షన్‌ని ఉపయోగించి ఇటువంటి పరివర్తన జరుగుతుంది – పట్టిక.ToRecords. దీన్ని నేరుగా ఫార్ములా బార్‌లో వర్తింపజేయడానికి, అక్కడ ఉన్న స్టెప్ కోడ్‌కి ఈ ఫంక్షన్‌ను జోడించండి మూల.

అది:

తరువాత:

Table.ToRecords ఫంక్షన్‌ను జోడించిన తర్వాత, మా పట్టిక యొక్క రూపాన్ని మార్చవచ్చు - ఇది రికార్డుల జాబితాగా మారుతుంది. ఏదైనా పదం పక్కన ఉన్న సెల్ బ్యాక్‌గ్రౌండ్‌లో క్లిక్ చేయడం ద్వారా వ్యక్తిగత రికార్డ్‌ల కంటెంట్‌లను వీక్షణ పేన్ దిగువన చూడవచ్చు రికార్డు (కానీ ఒక్క మాటలో కాదు!)

పైన పేర్కొన్న వాటితో పాటు, మరొక స్ట్రోక్‌ని జోడించడం అర్ధమే - మేము సృష్టించిన జాబితాను కాష్ (బఫర్) చేయడానికి. ఇది మన శోధన జాబితాను మెమరీలోకి ఒకసారి లోడ్ చేయమని పవర్ క్వెరీని బలవంతం చేస్తుంది మరియు దానిని భర్తీ చేయడానికి మేము తర్వాత దాన్ని యాక్సెస్ చేసినప్పుడు దాన్ని మళ్లీ లెక్కించదు. దీన్ని చేయడానికి, మా సూత్రాన్ని మరొక ఫంక్షన్‌లో చుట్టండి - జాబితా.బఫర్:

అటువంటి కాషింగ్ క్లియర్ చేయబడే పెద్ద మొత్తంలో ప్రారంభ డేటాతో (చాలా సార్లు!) వేగంలో చాలా గుర్తించదగిన పెరుగుదలను ఇస్తుంది.

ఇది హ్యాండ్‌బుక్ తయారీని పూర్తి చేస్తుంది.

ఇది క్లిక్ చేయడానికి మిగిలి ఉంది ఇల్లు - మూసివేయి మరియు లోడ్ చేయి - మూసివేయి మరియు లోడ్ చేయి... (హోమ్ — క్లోజ్&లోడ్ — క్లోజ్&లోడ్ టు..), ఒక ఎంపికను ఎంచుకోండి కేవలం కనెక్షన్‌ని సృష్టించండి (కనెక్షన్ మాత్రమే సృష్టించు) మరియు Excelకి తిరిగి వెళ్ళు.

దశ 2. డేటా పట్టికను లోడ్ చేస్తోంది

ఇక్కడ అంతా సామాన్యమైనది. రిఫరెన్స్ బుక్‌తో మునుపటిలా, మేము టేబుల్‌లోని ఏదైనా ప్రదేశానికి చేరుకుంటాము, ట్యాబ్‌పై క్లిక్ చేయండి సమాచారం బటన్ పట్టిక/పరిధి నుండి మరియు మా టేబుల్ సమాచారం పవర్ క్వెరీలోకి ప్రవేశిస్తుంది. స్వయంచాలకంగా జోడించిన దశ సవరించిన రకం (మార్చబడిన రకం) మీరు కూడా తీసివేయవచ్చు:

దానితో ప్రత్యేక సన్నాహక చర్యలు చేయవలసిన అవసరం లేదు మరియు మేము చాలా ముఖ్యమైన విషయానికి వెళ్తాము.

దశ 3. List.Accumulate ఫంక్షన్‌ని ఉపయోగించి భర్తీలను జరుపుము

కమాండ్‌ని ఉపయోగించి మన డేటా టేబుల్‌కి లెక్కించిన నిలువు వరుసను జోడిద్దాం కాలమ్‌ని కలుపుతోంది - అనుకూల కాలమ్ (నిలువు వరుసను జోడించండి - అనుకూల కాలమ్): మరియు తెరిచే విండోలో జోడించిన కాలమ్ పేరును నమోదు చేయండి (ఉదాహరణకు, చిరునామా సరిదిద్దబడింది) మరియు మా మేజిక్ ఫంక్షన్ జాబితా. కూడబెట్టు:

ఇది క్లిక్ చేయడానికి మిగిలి ఉంది OK - మరియు మేము చేసిన భర్తీలతో నిలువు వరుసను పొందుతాము:

ఇది గమనించండి:

• పవర్ క్వెరీ కేస్ సెన్సిటివ్ కాబట్టి, చివరి లైన్‌లో రీప్లేస్‌మెంట్ లేదు, ఎందుకంటే డైరెక్టరీలో మనకు “SPb” ఉంది, “SPb” కాదు.
• సోర్స్ డేటాలో ఒకేసారి భర్తీ చేయడానికి అనేక సబ్‌స్ట్రింగ్‌లు ఉంటే (ఉదాహరణకు, 7వ లైన్‌లో మీరు “S-Pb” మరియు “ప్రాస్పెక్టస్” రెండింటినీ భర్తీ చేయాలి), అప్పుడు ఇది ఎటువంటి సమస్యలను సృష్టించదు (సూత్రాలతో భర్తీ చేయడం వలె కాకుండా మునుపటి పద్ధతి).
• మూల వచనంలో (9వ పంక్తి) భర్తీ చేయడానికి ఏమీ లేకుంటే, లోపాలు ఏవీ జరగవు (మళ్లీ, సూత్రాల ద్వారా భర్తీ చేయడం నుండి కాకుండా).

అటువంటి అభ్యర్థన యొక్క వేగం చాలా చాలా మంచిది. ఉదాహరణకు, 5000 అడ్డు వరుసల పరిమాణంతో ప్రారంభ డేటా పట్టిక కోసం, ఈ ప్రశ్న సెకను కంటే తక్కువ వ్యవధిలో నవీకరించబడింది (బఫరింగ్ లేకుండా, దాదాపు 3 సెకన్లు!)

List.Acumulate ఫంక్షన్ ఎలా పనిచేస్తుంది

సూత్రప్రాయంగా, ఈ కథనం (నేను వ్రాయడానికి మరియు మీరు చదవడానికి) ముగింపు కావచ్చు. మీరు "హుడ్ కింద" ఎలా పనిచేస్తుందో మాత్రమే కాకుండా, అది ఎలా పనిచేస్తుందో కూడా అర్థం చేసుకోవాలనుకుంటే, మీరు కుందేలు రంధ్రంలోకి కొంచెం లోతుగా డైవ్ చేయాలి మరియు లిస్ట్. అక్యుములేట్ ఫంక్షన్‌తో వ్యవహరించాలి, ఇది మొత్తం బల్క్ రీప్లేస్‌మెంట్ చేసింది. మా కోసం పని చేయండి.

ఈ ఫంక్షన్ కోసం వాక్యనిర్మాణం:

=జాబితా. కూడబెట్టు(జాబితా, సీడ్, నిల్వ)

(ఇక్కడ

• జాబితా అనేది మనం మళ్ళిస్తున్న అంశాల జాబితా. 
• సీడ్ - ప్రారంభ స్థితి
• నిల్వ - జాబితా యొక్క తదుపరి మూలకంపై కొంత ఆపరేషన్ (గణితం, వచనం, మొదలైనవి) చేసే ఒక ఫంక్షన్ మరియు ప్రత్యేక వేరియబుల్‌లో ప్రాసెసింగ్ ఫలితాన్ని సంచితం చేస్తుంది.
  

సాధారణంగా, పవర్ క్వెరీలో ఫంక్షన్‌లను వ్రాయడానికి వాక్యనిర్మాణం ఇలా కనిపిస్తుంది:

(argument1, argument2, … argumentN) => వాదనలతో కూడిన కొన్ని చర్యలు

ఉదాహరణకు, సమ్మషన్ ఫంక్షన్‌ని ఇలా సూచించవచ్చు:

(a, b) => a + b

List.Accumulate కోసం, ఈ అక్యుమ్యులేటర్ ఫంక్షన్‌కు అవసరమైన రెండు ఆర్గ్యుమెంట్‌లు ఉన్నాయి (వాటికి ఏదైనా పేరు పెట్టవచ్చు, కానీ సాధారణ పేర్లు రాష్ట్ర и ప్రస్తుత, ఈ ఫంక్షన్ కోసం అధికారిక సహాయం వలె, ఇక్కడ:

• రాష్ట్ర – ఫలితం సేకరించబడిన వేరియబుల్ (దాని ప్రారంభ విలువ పైన పేర్కొన్నది సీడ్)
  
• ప్రస్తుత - జాబితా నుండి తదుపరి పునరావృత విలువ జాబితా
  

ఉదాహరణకు, కింది నిర్మాణం యొక్క తర్కం యొక్క దశలను పరిశీలిద్దాం:

=జాబితా. కూడబెట్టు({3, 2, 5}, 10, (స్టేట్, కరెంట్) => స్థితి + కరెంట్)

ఇది తాజాగా సేకరించినది రాష్ట్ర విలువ మా జాబితా. ఫలితంగా ఫంక్షన్ మరియు అవుట్‌పుట్‌లను కూడబెట్టుకోండి:

మీరు కొద్దిగా ఊహించినట్లయితే, List.Acumulate ఫంక్షన్‌ని ఉపయోగించి, మీరు Excel ఫంక్షన్ CONCATENATE (పవర్ క్వెరీలో, దాని అనలాగ్ అంటారు. వచనం.కలిపి) వ్యక్తీకరణను ఉపయోగించడం:

లేదా గరిష్ట విలువ కోసం కూడా శోధించండి (Excel యొక్క MAX ఫంక్షన్ యొక్క అనుకరణ, దీనిని పవర్ క్వెరీలో పిలుస్తారు List.Max):

అయితే, List.Accumulate యొక్క ప్రధాన లక్షణం సాధారణ టెక్స్ట్ లేదా సంఖ్యా జాబితాలను మాత్రమే ఆర్గ్యుమెంట్‌లుగా ప్రాసెస్ చేయగల సామర్థ్యం, ​​కానీ మరింత సంక్లిష్టమైన వస్తువులు - ఉదాహరణకు, జాబితాలు-నుండి-జాబితాలు లేదా జాబితాలు-రికార్డ్‌ల నుండి (హలో, డైరెక్టరీ!)

మా సమస్యలో భర్తీ చేసిన నిర్మాణాన్ని మళ్లీ చూద్దాం:

జాబితా. కూడబెట్టు(డైరెక్టరీ, [చిరునామా], (state,current) => Text.Replace(state, current[Find], current[Replace]) )

అసలు ఇక్కడ ఏం జరుగుతోంది?

1. ప్రారంభ విలువగా (సీడ్) మేము నిలువు వరుస నుండి మొదటి వికృతమైన వచనాన్ని తీసుకుంటాము [చిరునామా] మా టేబుల్: 199034, సెయింట్ పీటర్స్‌బర్గ్, str. బెరింగా, డి. 1
2. ఆపై జాబితా.అక్యుములేట్ జాబితా యొక్క మూలకాలపై ఒక్కొక్కటిగా పునరావృతమవుతుంది - హ్యాండ్బుక్. ఈ జాబితాలోని ప్రతి మూలకం "ఏమి కనుగొనాలి - దేనితో భర్తీ చేయాలి" లేదా మరో మాటలో చెప్పాలంటే, డైరెక్టరీలోని తదుపరి పంక్తి జత ఫీల్డ్‌లతో కూడిన రికార్డ్.
3. అక్యుమ్యులేటర్ ఫంక్షన్ వేరియబుల్‌లో ఉంచుతుంది రాష్ట్ర ప్రారంభ విలువ (మొదటి చిరునామా 199034, సెయింట్ పీటర్స్‌బర్గ్, str. బెరింగా, డి. 1) మరియు దానిపై ఒక అక్యుమ్యులేటర్ ఫంక్షన్‌ను నిర్వహిస్తుంది - ప్రామాణిక M-ఫంక్షన్‌ని ఉపయోగించి పునఃస్థాపన ఆపరేషన్ వచనం. భర్తీ చేయి (Excel యొక్క SUBSTITUTE ఫంక్షన్‌కి సారూప్యంగా ఉంటుంది). దీని వాక్యనిర్మాణం:

   Text.Replace(అసలు వచనం, మనం వెతుకుతున్నది, దేనితో భర్తీ చేస్తున్నాం)

   మరియు ఇక్కడ మనకు ఉన్నాయి:

• రాష్ట్ర ఇది మా మురికి చిరునామా రాష్ట్ర (అక్కడికి చేరుకోవడం సీడ్)
  
• ప్రస్తుత[శోధన] - ఫీల్డ్ విలువ కనుగొనేందుకు జాబితా యొక్క తదుపరి పునరావృత నమోదు నుండి డైరెక్టరీ, ఇది వేరియబుల్‌లో ఉంటుంది ప్రస్తుత
• ప్రస్తుత[భర్తీ] - ఫీల్డ్ విలువ సబ్స్టిట్యూట్ జాబితా యొక్క తదుపరి పునరావృత నమోదు నుండి డైరెక్టరీలో పడుకోవడం ప్రస్తుత

అందువలన, ప్రతి చిరునామా కోసం, డైరెక్టరీలోని అన్ని పంక్తుల గణన యొక్క పూర్తి చక్రం ప్రతిసారీ అమలు చేయబడుతుంది, [కనుగొను] ఫీల్డ్ నుండి వచనాన్ని [భర్తీ] ఫీల్డ్ నుండి విలువతో భర్తీ చేస్తుంది.

మీకు ఆలోచన వచ్చిందని ఆశిస్తున్నాను 🙂

• ఫార్ములాలను ఉపయోగించి జాబితాలోని వచనాన్ని బల్క్ రీప్లేస్ చేయండి
  
• పవర్ క్వెరీలో రెగ్యులర్ ఎక్స్‌ప్రెషన్స్ (RegExp).