Notera
Detta är en av en serie artiklar som beskriver lösningar som skickats in för Podcast 2316-utmaningen.
I mitt ursprungliga dataproblem stötte jag på ett problem mycket tidigt i processen. De inkommande uppgifterna skulle ha många kolumner med en rubrik på Q1.
I min lösning skapade jag ett namngivet intervall "UglyData" och importerade det till Power Query. Detta leder till det olyckliga resultatet av att Power Query byter namn på mina kolumner till Q1_1.
Senare, efter att ha avaktiverat, var jag tvungen att extrahera de två vänstra karaktärerna från dessa rubriker.
Det fanns tre separata lösningar på detta problem:
- Wyn Hopkins och degradera rubriker
- MF Wong och avmarkera Mitt bord har rubriker (föreslås också av Peter Bartholomew)
- Jason M och radera helt enkelt Promoted Headers (föreslås också av Ondřej Malinský och Excel MVP John MacDougall)
Den första innovationen var från Wyn Hopkins på Access Analytic. I stället för ett namngivet intervall konverterade Wyn data till en tabell med Ctrl + T. Vid denna tidpunkt skedde rubrikerna, eftersom Excel konverterade rubrikerna till:
När Wyn tog in data till Power Query öppnade han sedan rullgardinsmenyn Använd första raden som rubriker och valde Använd rubriker som första rad. Jag insåg aldrig att det var där. Det skapar ett steg som heter Table.DemoteHeaders.
Men även med Wyns förbättring skulle han fortfarande behöva extrahera de två första tecknen från dessa rubriker.
Den andra innovationen är MF Wongs teknik. När han skapade bordet avmarkerade han Mitt bord har rubriker!
Detta ser till att Excel lämnar flera Q1-rubriker ensamma och det finns inget behov av att extrahera det extra suffixet senare.
Jag förstår att det finns människor i läget "Jag älskar bord". MF Wongs video visade hur han kunde lägga till nya anställda till höger om data och tabellen expanderar automatiskt. Det finns många bra skäl att använda tabeller.
Men eftersom jag älskar delsummor, anpassade vyer och filtrerar efter markering brukar jag inte använda tabeller. Så jag uppskattar lösningen från Jason M. Han behöll uppgifterna som det namngivna intervallet för UglyData. Så snart han importerade data till Power Query raderade han dessa två steg:
Nu, med informationen helt enkelt i rad 1, finns det inget krångel med många kolumner som heter Q1.
Här är Wyn Hopkins kod som visar 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"Gå tillbaka till huvudsidan för Podcast 2316-utmaningen.
Läs nästa artikel i denna serie: Power Query: Radera detta, Radera dem eller ta bort ingenting ?.
