Dynamicky odklopte více sloupců v LuckyTemplates

V tomto tutoriálu se vrátíme k tématu, které jsem probral dříve, o tom, jak zrušit pivotování více sloupců v Power Query pomocí List.Zip. Ptal se člen LuckyTemplates najak udělat tuto metodu dynamickou. Celé video tohoto tutoriálu si můžete prohlédnout ve spodní části tohoto blogu.

Označil jsem to jako pokročilé, protože budeme psát řadu M funkcí a některé z nich pro vás mohou být dokonce nové.

Po tom, co bylo řečeno, přejděme k Power Query.

Obsah

• Data V Tabulkovém Formátu
• Změna kódu pro dynamické uvolnění více sloupců
• Dynamicky uvolnit více sloupců: Přidání vlastní logiky sloupců
• Závěr

Data V Tabulkovém Formátu

Jak můžete vidět, tato data již byla převedena do vhodného formátu tabletu vhodného pro analýzu. Tento proces, i když je velmi důležitý, zde nebudeme popisovat, ale stručně si projdeme každý krok.

Ve zdrojovém kroku vidíme neustálé opakování hodnotových párů, což jsou hodiny a kontakty pro každý z výše uvedených atributů.

V části Aplikované kroky můžete vidět zbytek kroků. Odstranili jsme některé horní řádky, odstranili prázdné sloupce, vyčistili naše textové hodnoty a vyplnili je. Poté jsme transponovali naše data, odstranili prázdný záznam, sloučili sloupce jedna a dva, znovu vyčistili naše textové hodnoty a transponovali naše data zpět.

Vytvořili jsme vlastní krok, který prozkoumáme podrobněji později, extrahovali jsme hodnoty z našeho seznamu a nakonec jsme změnili typ.

Podívejme se na celý kód. To, co nás zde zajímá, je funkce List.Zip . Krátká rekapitulace, vezme jeden seznam jako a a pak vrátí seznam. V důsledku toho vidíme, že tento jediný seznam v tomto případě obsahuje tři samostatné seznamy.

Pokud prozkoumáme první seznam, obsahuje pevně zakódované názvy atributů, pouze textové hodnoty, které byly zapsány. Druhý seznam obsahuje pevně zakódované odkazy na sloupce pro hodnoty hodin. Konečný seznam obsahuje pevně zakódované odkazy na sloupce pro kontaktní hodnoty.

List.Zip poté vezme prvek z každého z těchto tří seznamů a zkombinuje je na základě jejich pozice v novém seznamu. Například každý první prvek z těchto seznamů se sloučí do nového seznamu. Totéž se stane pro všechny hodnoty uvedené na druhém místě v tomto seznamu a tak dále.

Pevně ​​zakódované hodnoty mohou být znepokojivé, pokud je nepravděpodobné, že se atributy v průběhu času změní. Pokud tedy příště nebudou přítomny všechny položky nebo existují nové položky, můžete se dostat do problémů.

Změna kódu pro dynamické uvolnění více sloupců

Jak tedy uděláme tuto dynamiku?

První věc, kterou udělám, je projít kód, abych zjistil, zda existuje místo, kde můžeme tyto hodnoty atributů vyzvednout, aniž bychom k tomu museli navrhnout samostatný krok. Takže se v kódu vrátíme zpět a tento krok RemoveNull je perfektní.

Můžete vidět, že zde jsou uvedeny všechny hodnoty atributů ve sloupci jedna. K jejich extrakci můžeme použít uživatelské rozhraní.

Klikněte tedy pravým tlačítkem na záhlaví a dole vyberte Přidat jako nový dotaz .

Dostáváme tento seznam a to je dobrá zpráva, protože chceme nahradit pevně zakódovaný seznam, který jsme viděli dříve, dynamickým. Vše, co musíme udělat, je udělat pořádek v hodnotách.

Začněme odstraněním těchto nul. Do řádku vzorců tedy přidáme List.RemoveNulls . Jak nyní vidíte, všechny položky se opakují, a to kvůli párům hodnot. Je uveden jednou pro hodiny.

A pak znovu, pro kontakty, abychom odstranili tyto duplikáty, můžeme přidat List.Distinct . Nyní jsme vytvořili tuto logiku a vše, co musíme udělat, je zkopírovat a vložit zpět do našeho vzorového dotazu.

Ve vzorovém dotazu otevřete Rozšířený editor a vytvořte nový název proměnné. Říkejme tomu AttributeList . Poté vložíme kód a dáme čárku.

Nyní se vraťme k vlastnímu kroku. V List.Zip můžeme tento první seznam nahradit názvem naší proměnné (AttributeList).

Nyní se blíže podíváme na druhý seznam. Víme, že odkazuje na názvy sloupců a každý z těchto názvů sloupců začíná stejným atributem, který již v tomto seznamu máme, za ním následuje mezera a text „hodiny“.

Stačí ke každé položce přidat příponu. Vraťme se tedy o krok zpět a přidejte vlastní sloupec, abychom vytvořili určitou logiku.

Dynamicky uvolnit více sloupců: Přidání vlastní logiky sloupců

Je to jen dočasný sloupec, takže mu nemusíme dávat správný název. Jakmile budeme hotovi, odstraníme to.

Pro změnu hodnot v seznamu můžeme použít funkci List.Transform . Potřebujeme transformovat hodnoty v našem seznamu atributů , takže to vložím.

Zde v záhlaví vidíme, že textové hodnoty začínají velkým písmenem a zbytek jsou malá písmena. Náš atribut obsahuje pouze slovo s velkým písmenem, takže musíme transformovat i tuto textovou hodnotu. A můžeme použít podtržítko pro přístup ke každé položce seznamu a přidáme tuto příponu.

Tím získáme v našem sloupci hodnotu seznamu. Nezáleží na tom, který z nich vyberete, ale stačí kliknout na stranu do bílého místa v kterémkoli z nich zde.

Pod stránkou vidíme náhled tohoto seznamu, který jsme vytvořili. Takže vidíte, že tato záhlaví sloupců jsou nyní správně vložena tak, aby odpovídala záhlavím sloupců, která zde vidíme u tohoto seznamu.

Nyní máme prostředky k identifikaci polí z našeho záznamu, ke kterým chceme mít přístup. Otevřeme tedy znovu dialogové okno našeho vlastního sloupce a přidáme Record.SelectFields . Chce to záznam jako záznam, takže toto podtržítko můžeme použít k přístupu k aktuálnímu záznamu, na kterém jsme. A pak jsme mu dali seznam se všemi těmito názvy polí, takže přidejte uzavírací závorku a stiskněte OK .

Takže už nemáme seznam, ale nyní dostáváme záznam. Pojďme znovu kliknout na stranu do bílého prostoru a zobrazit obsah tohoto záznamu. Zde vidíme, že uvádí názvy polí záznamu a hodnoty polí záznamu.

Nás ale zajímají pouze hodnoty pole záznamu, takže je musíme extrahovat. Vraťme se zpět do dialogového okna vlastního sloupce a přidejte Record.FieldsValues ​​.

Nyní dostáváme znovu seznam a již ne záznam. Znovu klikneme na stranu do bílého prostoru a vidíte, že nyní máme seznam pouze s těmito hodnotami. Tak to vypadá dobře.

Zkopírujeme úplnou logiku, kterou jsme vytvořili, kromě poslední závorky. A nyní můžeme odstranit náš dočasný sloupec, protože jej již nepotřebujeme.

Na našem řádku vzorců vybereme náš druhý seznam a nahradíme jej naší logikou.

Abychom vytvořili seznam s hodnotami polí záznamu pro kontakty, musíme změnit pouze příponu. Můžeme tedy vybrat i ten poslední seznam a vložit do něj naši logiku.

Pak to místo hodin změňme na kontakty .

Bez chyb a vypadá to dobře. Toto je nyní dynamické řešení pro odklopení více sloupců.

Závěr

Takto můžete dynamicky uvolnit několik sloupcůpomocí List.Zip.

Pokud přijde nový atribut, bude automaticky převzat seznamem atributů. A když položka již neexistuje, nebude na tomto seznamu přítomna.

Doufám, že se vám to líbilo. Podívejte se na celé video níže, kde najdete další podrobnosti o tomto tutoriálu, a podívejte se na odkazy níže, kde najdete další obsah týkající se unpivot více sloupců v LuckyTemplates.

Vše nejlepší

Melissa