Poznámka
Toto je jeden z řady článků s podrobnými řešeními zaslanými pro výzvu Podcast 2316.
V mém původním problému s tvarováním dat jsem narazil na problém velmi brzy v procesu. Příchozí data měla mít mnoho sloupců s nadpisem Q1.
Ve svém řešení jsem vytvořil pojmenovanou oblast „UglyData“ a importoval ji do Power Query. To vedlo k nešťastnému výsledku přejmenování mých sloupců Power Query na Q1_1.
Později, po zrušení motivu, jsem musel z těchto záhlaví extrahovat jen levé dvě postavy.
Existovaly tři samostatné řešení tohoto problému:
- Wyn Hopkins a snížit úroveň záhlaví
- MF Wong a zrušte zaškrtnutí možnosti Můj stůl má záhlaví (také navrhl Peter Bartholomew)
- Jason M a jednoduše smazat sponzorovaná záhlaví (také navrhl Ondřej Malinský a Excel MVP John MacDougall)
První inovace byla od Wyn Hopkins ve společnosti Access Analytic. Místo pojmenovaného rozsahu Wyn převedl data do tabulky pomocí Ctrl + T. V tomto okamžiku došlo k poškození nadpisů, protože Excel převedl nadpisy na:
Jakmile Wyn vzal data do Power Query, otevřel rozevírací nabídku Použít první řádek jako záhlaví a vybral Použít záhlaví jako první řádek. Nikdy jsem si neuvědomil, že to tam je. Vytvoří krok s názvem Table.DemoteHeaders.
Ale is Wynovým vylepšením by ještě později musel z těchto hlaviček extrahovat první 2 znaky.
Druhou novinkou je technika MF Wonga. Když vytvořil tabulku, zrušil zaškrtnutí položky Můj stůl má záhlaví!
Tím je zajištěno, že Excel ponechá několik záhlaví Q1 na pokoji a není nutné extrahovat dodatečnou příponu později.
Chápu, že v táboře „Miluji stoly“ jsou lidé. Video MF Wong demonstrovalo, jak by mohl přidat nové zaměstnance napravo od dat a tabulka se automaticky rozšiřuje. Existuje mnoho dobrých důvodů pro použití tabulek.
Ale protože miluji mezisoučty, vlastní zobrazení a filtrování podle výběru, nemám tendenci používat tabulky. Oceňuji tedy řešení od Jasona M. Data uchovával jako pojmenovaný rozsah UglyData. Jakmile importoval data do Power Query, odstranil tyto dva kroky:
Nyní, s daty jednoduše v řádku 1, není problém s mnoha sloupci zvanými Q1.
Zde je kód Wyn Hopkin ukazující DemotedHeaders:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Vraťte se na hlavní stránku výzvy Podcast 2316.
Přečtěte si další článek v této sérii: Power Query: Smazat toto, Smazat ty nebo smazat nic ?.
