Sostituzzjoni tat-test bl-ingrossa f'Power Query b'funzjoni List.Accumulate

Kif malajr u bl-ingrossa tissostitwixxi t-test skont il-lista ta 'referenza b'formuli - diġà rranġajna. Issa ejja nippruvaw nagħmluh f'Power Query.

Kif jiġri spiss iwettqu dan il-kompitu huwa ħafna aktar faċli milli tispjega għaliex jaħdem, imma ejja nippruvaw nagħmlu t-tnejn 🙂

Għalhekk, għandna żewġ tabelli dinamiċi "intelliġenti" maħluqa minn firxiet ordinarji b'shortcut tat-tastiera Ctrl+T jew tim Home – Format bħala tabella (Id-dar — Format bħala Tabella):

Ċempilt l-ewwel mejda Data, it-tieni tabella - Direttorjuuża qasam Isem tal-mejda (Isem tal-mejda) tab kostruttur (Disinn).

Kompitu: ibdel fl-indirizzi fit-tabella Data l-okkorrenzi kollha minn kolonna Issib Manwal lill-kontropartijiet korretti korrispondenti tagħhom mill-kolonna Sostitut. Il-bqija tat-test fiċ-ċelloli għandhom jibqgħu mhux mittiefsa.

Pass 1. Tagħbija d-direttorju f'Power Query u dawwarha f'lista

Wara li ssettja ċ-ċellula attiva għal kwalunkwe post fit-tabella ta 'referenza, ikklikkja fuq it-tab Data (Data)jew fuq it-tab Mistoqsija dwar l-Enerġija (jekk għandek verżjoni l-qadima ta’ Excel u installajt Power Query bħala add-in fuq tab separata) fuq il-buttuna Minn tabella/firxa (Mill-Tabella/Firxa).

It-tabella ta' referenza titgħabba fl-editur tal-mistoqsijiet tal-Power Query:

Sabiex ma jinterferixxix, pass miżjud awtomatikament tip modifikat (Tip Mibdul) fil-pannell tal-lemin, il-passi applikati jistgħu jitħassru b'mod sikur, u jħallu biss il-pass sors (Sors):

Issa, biex inwettqu aktar trasformazzjonijiet u sostituzzjonijiet, għandna bżonn inbiddlu din it-tabella f'lista (lista).

Digressjoni lirika

• Tabella hija firxa bidimensjonali li tikkonsisti f'diversi ringieli u kolonni.
• Rekord (Rekord) – string-array-dimensjonali waħda, li tikkonsisti f'diversi oqsma-elementi b'ismijiet, pereżempju [Isem = "Masha", Sess = "f", Età = 25]
  
• Lista – kolonna ta' firxa ta' dimensjoni waħda, li tikkonsisti f'diversi elementi, pereżempju {1, 2, 3, 10, 42} or { “Fidi Tama Imħabba” }
  

Biex issolvi l-problema tagħna, se nkunu primarjament interessati fit-tip Lista.

Il-trick hawnhekk huwa li l-oġġetti tal-lista f'Power Query jistgħu jkunu mhux biss numri jew test banali, iżda wkoll listi jew rekords oħra. Huwa f'lista delikata (lista), li tikkonsisti minn rekords (rekords) li għandna bżonn inbiddlu d-direttorju tagħna. Fin-notazzjoni sintattika tal-Power Query (entrati f'parentesi kwadri, listi f'parentesi kaboċċi) dan ikun jidher bħal:

{

    [ Sib = “St. Petersburg”, Ibdel = “St. Pietruburgu”] ,

    [ Sib = “St. Petersburg”, Ibdel = “St. Pietruburgu”] ,

    [ Sib = “Pietru”, Ibdel = “St. Pietruburgu”] ,

eċċ.

}

Trasformazzjoni bħal din titwettaq bl-użu ta’ funzjoni speċjali tal-lingwa M mibnija fil-Power Query – Tabella.ToRecords. Biex tapplikaha direttament fil-bar tal-formula, żid din il-funzjoni mal-kodiċi tal-pass hemmhekk sors.

Kienet:

Wara:

Wara li żżid il-funzjoni Table.ToRecords, id-dehra tat-tabella tagħna tinbidel - tinbidel f'lista ta 'rekords. Il-kontenut tar-rekords individwali jista' jidher fil-qiegħ tal-panew tal-vista billi tikklikkja fl-isfond taċ-ċellula ħdejn kwalunkwe kelma rekord (iżda mhux f'kelma waħda!)

Minbarra dan ta 'hawn fuq, jagħmel sens li żżid stroke waħda aktar - biex tiġbor fil-cache (buffer) il-lista maħluqa tagħna. Dan se jġiegħel lill-Power Query jgħabbi l-lista ta' tiftix tagħna darba fil-memorja u ma terġax tikkalkulaha mill-ġdid meta wara jkollna aċċess għaliha biex tibdilha. Biex tagħmel dan, wrap formula tagħna f'funzjoni oħra - Lista.Buffer:

Tali caching se jagħti żieda notevoli ħafna fil-veloċità (b'diversi drabi!) B'ammont kbir ta 'dejta inizjali li trid titneħħa.

Dan itemm il-preparazzjoni tal-manwal.

Fadal biex tikklikkja fuq Id-dar – Agħlaq u Tagħbija – Agħlaq u Tagħbija sa… (Id-dar — Agħlaq&Tagħbija — Agħlaq&Tagħbija għal..), agħżel għażla Oħloq biss konnessjoni (Oħloq konnessjoni biss) u rritorna lejn Excel.

Pass 2. Tagħbija tat-tabella tad-dejta

Kollox huwa banal hawn. Bħal qabel bil-ktieb ta 'referenza, inqumu għal kwalunkwe post fit-tabella, ikklikkja fuq it-tab Data buttuna Minn Tabella/Firxa u l-mejda tagħna Data jidħol fis-Power Query. Pass miżjud awtomatikament tip modifikat (Tip Mibdul) tista' wkoll tneħħi:

L-ebda azzjonijiet preparatorji speċjali ma huma meħtieġa li jsiru magħha, u ngħaddu għall-iktar ħaġa importanti.

Pass 3. Wettaq sostituzzjonijiet billi tuża l-funzjoni List.Accumulate

Ejja nżidu kolonna kkalkulata mat-tabella tad-dejta tagħna billi tuża l-kmand Żieda ta 'Kolonna - Kolonna Custom (Żid kolonna — Kolonna personalizzata): u daħħal l-isem tal-kolonna miżjuda fit-tieqa li tiftaħ (per eżempju, indirizz korrett) u l-funzjoni maġika tagħna Lista.Akkumula:

Fadal biex tikklikkja fuq OK – u nġibu kolonna bis-sostituzzjonijiet li saru:

Innota li:

• Peress li l-Power Query hija sensittiva għall-każi, ma kien hemm l-ebda sostituzzjoni fil-linja ta 'qabel l-aħħar, għaliex fid-direttorju għandna "SPb", mhux "SPb".
• Jekk hemm diversi substrings biex jissostitwixxu f'daqqa fid-dejta tas-sors (pereżempju, fis-7 linja għandek bżonn tissostitwixxi kemm "S-Pb" kif ukoll "Prospectus"), allura dan ma joħloq l-ebda problemi (b'differenza mis-sostituzzjoni b'formuli minn il-metodu preċedenti).
• Jekk m'hemm xejn x'jissostitwixxi fit-test tas-sors (9th line), allura ma jseħħu l-ebda żbalji (b'differenza, għal darb'oħra, minn sostituzzjoni b'formuli).

Il-veloċità ta 'talba bħal din hija ħafna, ħafna deċenti. Pereżempju, għal tabella ta 'dejta inizjali b'daqs ta' 5000 ringiela, din il-mistoqsija ġiet aġġornata f'inqas minn sekonda (mingħajr buffering, bil-mod, madwar 3 sekondi!)

Kif taħdem il-funzjoni List.Accumulate

Fil-prinċipju, dan jista 'jkun it-tmiem (għalija nikteb, u għalik biex taqra) dan l-artikolu. Jekk trid mhux biss tkun kapaċi, iżda wkoll tifhem kif taħdem "taħt il-barnuża", allura jkollok tgħaddas ftit aktar fil-fond fit-toqba tal-fenek u tittratta l-funzjoni List.Accumulate, li għamlet is-sostituzzjoni kollha bl-ingrossa. taħdem għalina.

Is-sintassi għal din il-funzjoni hija:

=Lista.Akkumula (lista, żerriegħa, akkumulatur)

fejn

• lista hija l-lista li l-elementi tagħha qed nirrepetu fuqhom. 
• żerriegħa – stat inizjali
• akkumulatur – funzjoni li twettaq xi operazzjoni (matematika, test, eċċ.) fuq l-element li jmiss tal-lista u takkumula r-riżultat tal-ipproċessar f'varjabbli speċjali.
  

B'mod ġenerali, is-sintassi għall-funzjonijiet tal-kitba f'Power Query tidher bħal din:

(argument1, argument2, … argumentN) => xi azzjonijiet b'argumenti

Pereżempju, il-funzjoni ta' somma tista' tiġi rappreżentata bħala:

(a, b) => a + b

Għal List.Accumulate , din il-funzjoni akkumulatur għandha żewġ argumenti meħtieġa (jistgħu jissemmew xi ħaġa, iżda l-ismijiet tas-soltu huma kienu и kurrenti, bħal fl-għajnuna uffiċjali għal din il-funzjoni, fejn:

• kienu – varjabbli fejn ir-riżultat huwa akkumulat (il-valur inizjali tiegħu huwa dak imsemmi hawn fuq żerriegħa)
  
• kurrenti – il-valur ripetut li jmiss mil-lista lista
  

Pereżempju, ejja nagħtu ħarsa lejn il-passi tal-loġika tal-kostruzzjoni li ġejja:

=Lista.Akkumula ({3, 2, 5}, 10, (stat, kurrenti) => stat + kurrenti)

Dan huwa l-aħħar akkumulat kienu il-valur huwa l-funzjoni List.Accumulate tagħna u l-outputs bħala riżultat:

Jekk tiffantasizza ftit, imbagħad tuża l-funzjoni Lista.Akkumula, tista 'tissimula, pereżempju, il-funzjoni Excel CONCATENATE (f'Power Query, l-analogu tiegħu jissejjaħ Test.Għaqqad) billi tuża l-espressjoni:

Jew saħansitra fittex il-valur massimu (imitazzjoni tal-funzjoni MAX ta 'Excel, li f'Power Query tissejjaħ Lista.Max):

Madankollu, il-karatteristika ewlenija ta 'List.Accumulate hija l-abbiltà li tipproċessa mhux biss test sempliċi jew listi numeriċi bħala argumenti, iżda oġġetti aktar kumplessi - per eżempju, listi-minn-listi jew listi-minn-rekords (hello, Direttorju!)

Ejja nħarsu mill-ġdid lejn il-kostruzzjoni li wettqet is-sostituzzjoni fil-problema tagħna:

Lista.Akkumula(Direttorju, [Indirizz], (stat,kurrent) => Test.Ssostitwixxi(stat, kurrenti[Sib], kurrenti[Ssostitwixxi]) )

X'qed jiġri verament hawn?

1. Bħala valur inizjali (żerriegħa) nieħdu l-ewwel test goff mill-kolonna [Indirizz] il-mejda tagħna: 199034, San Pietruburgu, str. Beringa, d. 1
2. Imbagħad List.Accumulate itenni fuq l-elementi tal-lista wieħed wieħed - Manwal. Kull element ta 'din il-lista huwa rekord li jikkonsisti f'par ta' oqsma "X'għandek issib - X'għandek tissostitwixxi bi" jew, fi kliem ieħor, il-linja li jmiss fid-direttorju.
3. Il-funzjoni akkumulatur tpoġġi f'varjabbli kienu valur inizjali (l-ewwel indirizz 199034, San Pietruburgu, str. Beringa, d. 1) u twettaq funzjoni ta 'akkumulatur fuqha - l-operazzjoni ta' sostituzzjoni bl-użu tal-funzjoni M standard Test.Ssostitwixxi (analogu għall-funzjoni SOSTITUT ta' Excel). Is-sintassi tagħha hija:

   Text.Replace (test oriġinali, dak li qed infittxu, dak li qed nissostitwixxu bi )

   u hawn għandna:

• kienu huwa l-indirizz maħmuġ tagħna, li jinsab fi kienu (assal hemm minn żerriegħa)
  
• kurrenti[Fittex] – valur tal-qasam Issib mid-dħul ripetut li jmiss tal-lista Direttorju, li tinsab fil-varjabbli kurrenti
• kurrenti[Ibdel] – valur tal-qasam Sostitut mid-dħul ripetut li jmiss tal-lista Direttorjutinsab ġewwa kurrenti

Għalhekk, għal kull indirizz, ċiklu sħiħ ta 'enumerazzjoni tal-linji kollha fid-direttorju jitmexxa kull darba, li jissostitwixxi t-test mill-qasam [Sib] bil-valur mill-qasam [Issostitwixxi].

Nispera li ħadt l-idea 🙂

• Ibdel it-test bl-ingrossa f'lista billi tuża formuli
  
• Espressjonijiet Regolari (RegExp) fil-Power Query