Bemærk
Dette er en af en række artikler, der beskriver løsninger sendt til Podcast 2316-udfordringen.
I mit oprindelige dataformingsproblem løb jeg ind i et problem meget tidligt i processen. De indgående data skulle have mange kolonner med en overskrift på Q1.
I min løsning oprettede jeg et navngivet område "UglyData" og importerede det til Power Query. Dette førte til det ulykkelige resultat af Power Query, der omdøbte mine kolonner til Q1_1.
Senere, efter at jeg havde fjernet drejningen, måtte jeg trække de to venstre tegn ud af disse overskrifter.
Der var tre separate løsninger på dette problem:
- Wyn Hopkins og nedrykningsoverskrifter
- MF Wong og fjern markeringen af My Table has Headers (også foreslået af Peter Bartholomew)
- Jason M og slet simpelthen Promoted Headers (også foreslået af Ondřej Malinský og Excel MVP John MacDougall)
Den første innovation var fra Wyn Hopkins hos Access Analytic. I stedet for et navngivet område konverterede Wyn dataene til en tabel ved hjælp af Ctrl + T. På dette tidspunkt blev skaden på overskrifterne gjort, da Excel konverterede overskrifterne til:
Når Wyn tog dataene ind i Power Query, åbnede han derefter rullemenuen Brug første række som overskrifter og valgte Brug overskrifter som første række. Jeg har aldrig indset, at dette var der. Det opretter et trin kaldet Table.DemoteHeaders.
Men selv med Wyns forbedring skulle han stadig senere trække de første 2 tegn ud af disse overskrifter.
Den anden innovation er MF Wongs teknik. Da han oprettede bordet, fjernede han markeringen fra Mit bord har overskrifter!
Dette sørger for, at Excel efterlader flere Q1-overskrifter alene, og at der ikke er behov for at udtrække det ekstra suffiks senere.
Jeg forstår, at der er mennesker i lejren "Jeg elsker borde". MF Wongs video demonstrerede, hvordan han kunne tilføje nye medarbejdere til højre for dataene, og tabellen udvides automatisk. Der er mange gode grunde til at bruge tabeller.
Men fordi jeg elsker subtotaler, brugerdefinerede visninger og filtrer efter valg, har jeg en tendens til ikke at bruge tabeller. Så jeg sætter pris på løsningen fra Jason M. Han opbevarede dataene som det navngivne interval for UglyData. Så snart han importerede dataene til Power Query, slettede han disse to trin:
Nu, med dataene simpelthen i række 1, er der intet besvær med mange kolonner kaldet Q1.
Her er Wyn Hopkins kode, der viser 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å tilbage til hovedsiden for Podcast 2316-udfordringen.
Læs den næste artikel i denne serie: Power Query: Slet dette, Slet dem eller slet ingenting ?.
