Ako dynamicky zlúčiť stĺpce v tabuľke Power Query

V tomto návode preskúmame zaujímavú otázku, ktorá bola nastolená nao tom, ako dynamicky zlúčiť meniaci sa počet stĺpcov v tabuľke Power Query. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Tento scenár sa zaoberá exportom nespracovaných údajov, ktoré spôsobujú rozliatie údajov do neznámeho počtu susedných stĺpcov. Keď sa pozriete na príklad, môžete vidieť, že popis sa prelial do stĺpca 4 a popis 2 bol rozdelený do dvoch susedných stĺpcov. Ale nebude to tak vždy. V ďalšom cykle by sa tento počet stĺpcov mohol zmeniť.

Skôr než prejdeme k riešeniu, pozrime sa na kód M, ktorý Power Query vygeneruje pri zlúčení stĺpcov.

Obsah

• Kód M tabuľky Power Query
• Zlúčenie meniaceho sa počtu stĺpcov v tabuľke Power Query
• Záver

Kód M tabuľky Power Query

Existuje niekoľko spôsobov, ako zlúčiť stĺpce. Jedným z nich je vybrať stĺpec s popisom a stlačiť kláves Shift alebo Ctrl a potom vybrať aj stĺpec 4. Potom kliknite pravým tlačidlom myši a vyberte možnosť Zlúčiť stĺpce .

Alebo navyberte zlúčiť stĺpce . A zobrazí sa toto dialógové okno, ktoré vám umožní vybrať oddeľovač.

Vo vyskakovacom okne vyberte Tab a môžete zadať nový názov stĺpca. V tomto prípade som to nazval Popis.

V riadku vzorcov vidíme M kód, ktorý tento transformačný krok vytvoril.

Ak sa riadok vzorcov na obrazovke nezobrazuje, prejdite na kartu Zobraziť a zapnite ju.

Funkcia, ktorú Power Query používa na zlúčenie stĺpcov, je teda Table.CombinedColumns . Prvým parametrom je tabuľka. Táto tabuľka je vrátená predchádzajúcim krokom v našom kóde, takže v časti Aplikované kroky uvidíte, že sa to nazýva Zdroj .

Potom napevno zakódoval názvy stĺpcov v zozname. Tu vidíte inicializátory zoznamu a medzi hodnotami v texte popis názvu stĺpca a stĺpec 4 s názvom stĺpca. Potom zavolá ďalšiu funkciu M, aby skombinovala textové hodnoty v týchto stĺpcoch.

A nakoniec odovzdá nový názov stĺpca ako text, aby sme ho mohli upraviť.

Ak teda chceme, aby táto funkcia dynamicky zlúčila meniaci sa počet stĺpcov, budeme musieť zmeniť hodnotu druhého parametra, ktorá teraz obsahuje zoznam pevne zakódovaných názvov stĺpcov.

Zlúčenie meniaceho sa počtu stĺpcov v tabuľke Power Query

Vrátim sa späť k môjmu dotazu na nespracované údaje a ak sa bližšie pozrieme na názvy stĺpcov, uvidíme, že každý zo stĺpcov je anonymný. Nemajú vlastné mená, ale všetky začínajú stĺpcom textu, za ktorým nasleduje číslo. Uvidíme, či to môžeme použiť. Najprv vytvorím referenciu kliknutím pravým tlačidlom myši sem a výberom položky Referencia .

A na získanie názvov stĺpcov do riadka vzorcov môžem pridať Table.ColumnNames . Táto funkcia vráti zoznam so všetkými názvami stĺpcov z tejto tabuľky. Premeňme to späť na tabuľku kliknutím na To Table .

V tomto vyskakovacom okne kliknite na tlačidlo OK.

Potom na karte Pridať stĺpec vyberiem Formát a potom Orezať .

Toto nie je transformácia, ktorú chcem vykonať, ale generuje pre mňa väčšinu kódu M. Jediné, čo musím urobiť, je.

V riadku vzorcov teda namiesto Text.Trim môžeme povedať if Text.StartsWith a potom ho nasmerovať na náš stĺpec 1 a ja to okamžite skopírujem. Chce to text, ktorý hľadáme, takže to bude stĺpec. Takže ak to začína textovým stĺpcom, potom chceme „null“ – pre všetko ostatné chceme čokoľvek, čo je v stĺpci 1. Tento stĺpec môžeme tiež premenovať, takže v riadku vzorcov ho namiesto Trim nazvime GroupColumn .

Teraz všetko, čo musíme urobiť, je naplniť ich týmito hodnotami. Takže kliknem pravým tlačidlom myši na hlavičku, vyberiem Vyplniť a Dole .

Keď teda nabudúce prídu údaje a počet stĺpcov sa zmení, táto skupina ich automaticky vyberie.

Premenujme tento dotaz, nazvem ho skupiny stĺpcov.

Uistime sa, že jeho načítanie bolo vypnuté, pretože toto je len podporný dotaz.

Môžem odkazovať na tento podporný dotaz a neskôr ho premenujem. Nechajme to zatiaľ. Takže ak si pamätáte, začali sme zlúčením dvoch stĺpcov, čím sme vytvorili pevne zakódovaný zoznam s názvami stĺpcov , ale teraz môžeme filtrovať podľa popisu v našom GroupColumn .

Takže ak urobíme tento filter v popise v stĺpci 1, dostaneme stĺpce, ktoré spĺňajú tieto kritériá.

Toto je však tabuľka a nie zoznam.pretože tieto stĺpce musíme zahrnúť do našej operácie zlúčenia. Ak to chcete urobiť, môžeme kliknúť pravým tlačidlom myši na hlavičku stĺpca 1 a vybrať položku Rozbaliť .

A teraz dostaneme zoznam. Dajme teda tomuto dotazu správny názov ( ListDescr ) a skontrolujme, či je zakázaný pri načítaní.

Teraz sa môžeme vrátiť k nášmu dotazu na výsledok a nahradiť tu druhý argument naším dynamickým zoznamom. Takže tu môžeme odkazovať na náš ListDescr .

Zopakujme to aj pre Popis 2. Prejdem na svoj dotaz ColumnGroups. Vytvorím ďalšiu referenciu a vyberiem stĺpce, ktoré potrebujem. Potom vyfiltrujem Popis 2, ktorý vráti tieto tri stĺpce. Pravým tlačidlom myši kliknem na svoju hlavičku a vyberiem možnosť Drill Down a premenujem aj tento dotaz ( ListDescr2 ).

Potom sa vrátim k dotazu na výsledok, vyberiem stĺpec Popis2 a stlačením klávesu Shift alebo Control vyberiete aj ďalší stĺpec (stĺpec2). Kliknem pravým tlačidlom myši na hlavičku a vyberiem si.

Vo vyskakovacom okne vyberiem Tab ako oddeľovač a nazvem to Popis2.

Len zmením názov stĺpca tu na riadku vzorcov. Zmením aj tento pevne zakódovaný zoznam zo zoznamu, ktorý sme práve vytvorili. A teraz, ako môžete vidieť, sa objavil aj stĺpec 8, ktorý sme predtým vylúčili.

Záver

V tomto blogu som vám ukázal, ako používať M kódy v Power Query na dynamické zlučovanie stĺpcov. Nie je to ťažké, pokiaľ viete, ako funguje Power Query a aké M kódy použiť.

Dúfam, že sa vám to páčilo. Viac súvisiaceho obsahu nájdete na nižšie uvedených odkazoch.

Všetko najlepšie!

Melissa