Power Query: Riešenie viacerých identických hlavičiek - tipy pre Excel

Poznámka

Toto je jeden zo série článkov s podrobnými riešeniami zaslanými k výzve Podcast 2316.

V mojom pôvodnom probléme s tvarovaním údajov som narazil na problém veľmi skoro v procese. Prichádzajúce údaje mali mať veľa stĺpcov s hlavičkou Q1.

Veľa stĺpcov

Vo svojom riešení som vytvoril pomenovaný rozsah „UglyData“ a importoval som ho do Power Query. To viedlo k neradostnému výsledku, keď Power Query premenoval moje stĺpce na Q1_1.

Premenované stĺpce

Neskôr, po zrušení zrušenia platnosti, som musel z týchto hlavičiek extrahovať iba ľavé dva znaky.

Existujú tri samostatné riešenia tohto problému:

  • Wyn Hopkins a degradovať hlavičky
  • MF Wong a zrušte začiarknutie políčka Môj stôl má hlavičky (tiež navrhuje Peter Bartolomej)
  • Jason M a jednoducho odstráni Propagované hlavičky (tiež navrhuje Ondřej Malinský a Excel MVP John MacDougall)

Prvou novinkou bola Wyn Hopkins zo spoločnosti Access Analytic. Namiesto pomenovaného rozsahu Wyn konvertoval údaje do tabuľky pomocou kombinácie klávesov Ctrl + T. V tomto okamihu došlo k poškodeniu nadpisov, pretože Excel nadpisy previedol na:

Prevedené na tabuľku: Ctrl + T

Keď Wyn vzal údaje do Power Query, otvoril rozbaľovaciu ponuku Použiť prvý riadok ako hlavičku a vybral Použiť hlavičky ako prvý riadok. Nikdy som si neuvedomil, že to tam je. Vytvára krok s názvom Table.DemoteHeaders.

Ako prvý riadok použite hlavičky

Ale aj s vylepšením Wyn by z týchto hlavičiek musel neskôr extrahovať prvé 2 znaky.

Druhou novinkou je technika MF Wong. Keď vytvoril stôl, zrušil začiarknutie políčka Môj stôl obsahuje hlavičky!

Môj stôl má hlavičky

Takto je zaistené, že program Excel ponechá viac hlavičiek Q1 na pokoji a nie je potrebné extrahovať dodatočnú príponu neskôr.

Viaceré hlavičky Q1

Rozumiem, že v tábore „Milujem stoly“ sú ľudia. Video MF Wong demonštrovalo, ako môže pridať nových zamestnancov napravo od údajov a tabuľka sa automaticky rozšíri. Existuje veľa dobrých dôvodov na použitie tabuliek.

Ale pretože milujem medzisúčty, vlastné zobrazenia a filtrovať podľa výberu, zvyknem tabuľky používať. Oceňujem teda riešenie od Jasona M. Údaje ponechal ako pomenovaný rozsah UglyData. Hneď ako importoval údaje do aplikácie Power Query, odstránil tieto dva kroky:

Vymazané kroky

Teraz, keď sú údaje jednoducho v riadku 1, neexistuje problém s mnohými stĺpcami nazývanými Q1.

Veľa stĺpcov Q1

Tu je kód Wyn Hopkinovej, ktorý zobrazuje 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"

Vráťte sa na hlavnú stránku výzvy Podcast 2316.

Prečítajte si nasledujúci článok v tejto sérii: Power Query: Odstrániť toto, Odstrániť tieto alebo nezmazať nič ?.

Zaujímavé články...