Poizvedba Power: Obravnavanje več enakih glav - Excelovi nasveti

Kazalo

Opomba

To je eden iz serije člankov, ki podrobno opisujejo rešitve, poslane za izziv Podcast 2316.

Pri mojem izvirnem problemu oblikovanja podatkov sem zelo zgodaj naletel na težavo. Vhodni podatki bodo imeli veliko stolpcev z naslovom Q1.

Veliko stolpcev

V svoji rešitvi sem ustvaril imenovani obseg "UglyData" in ga uvozil v Power Query. To je pripeljalo do nesrečnega rezultata Power Query, ki je moje stolpce preimenoval v Q1_1.

Preimenovani stolpci

Pozneje, po odklopu, sem moral iz teh glav izvleči le dva znaka.

Za ta problem so obstajale tri ločene rešitve:

  • Wyn Hopkins in Demote Headers
  • MF Wong in počistite polje My Table Has Headers (predlagal tudi Peter Bartholomew)
  • Jason M in preprosto izbrišite promovirane glave (predlagata jih tudi Ondřej Malinský in Excel MVP John MacDougall)

Prva novost je bila Wyn Hopkins pri Access Analytic. Namesto imenovanega obsega je Wyn podatke pretvoril v tabelo s pomočjo Ctrl + T. Na tej točki je bila škoda naslovom narejena, saj je Excel naslove pretvoril v:

Pretvorjeno v tabelo: Ctrl + T

Ko je Wyn podatke vnesel v Power Query, je nato odprl spustni meni Uporabi prvo vrstico kot glave in izbral Uporabi glave kot prva vrstica. Nikoli nisem dojel, da je to tam. Ustvari korak, imenovan Table.DemoteHeaders.

Uporabite glave kot prvo vrstico

Toda tudi z Wynovim izboljšanjem bi moral kasneje iz teh glav izvleči prva dva znaka.

Druga novost je tehnika MF Wonga. Ko je ustvaril tabelo, je potrdil polje Moja tabela ima glave!

Moja tabela ima glave

Tako zagotovite, da Excel pusti več naslovov Q1 pri miru in dodatne pripone ni treba pozneje izvleči.

Več glav Q1

Razumem, da so v taborišču "Obožujem mize" ljudje. Video MF Wonga je pokazal, kako lahko doda nove zaposlene na desni strani podatkov in tabela se samodejno razširi. Obstaja veliko tehtnih razlogov za uporabo tabel.

Ker pa imam rad vmesne seštevke, poglede po meri in filtriranje po izbiri, ponavadi ne uporabljam tabel. Zato cenim rešitev Jasona M. Podatke je hranil kot imenovani obseg UglyData. Takoj ko je uvozil podatke v Power Query, je izbrisal ta dva koraka:

Izbrisani koraki

Zdaj, ko so podatki preprosto v vrstici 1, ni težav, da bi bilo veliko stolpcev, imenovanih Q1.

Veliko stolpcev Q1

Tu je koda Wyn Hopkin, ki prikazuje 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"

Vrnite se na glavno stran izziva Podcast 2316.

Preberite naslednji članek v tej seriji: Power Query: to izbrišite, izbrišite ali nič ne izbrišite ?.

Zanimive Članki...