Merk
Dette er en av en serie artikler som beskriver løsninger som er sendt inn for Podcast 2316-utfordringen.
I det opprinnelige dataproblemet mitt, fikk jeg et problem veldig tidlig i prosessen. Innkommende data skulle ha mange kolonner med en overskrift på Q1.
I løsningen min opprettet jeg et navngitt utvalg "UglyData" og importerte det til Power Query. Dette førte til det ulykkelige resultatet av at Power Query omdøpte kolonnene mine til Q1_1.
Senere, etter at jeg ikke hadde svingt, måtte jeg trekke ut bare to venstre tegn fra disse overskriftene.
Det var tre separate løsninger på dette problemet:
- Wyn Hopkins og Demote Headers
- MF Wong og fjern merket for My Table Has Headers (også foreslått av Peter Bartholomew)
- Jason M og slett bare Promoted Headers (også foreslått av Ondřej Malinský og Excel MVP John MacDougall)
Den første nyvinningen var fra Wyn Hopkins hos Access Analytic. I stedet for et navngitt område konverterte Wyn dataene til en tabell ved hjelp av Ctrl + T. På dette tidspunktet ble skaden på overskriftene gjort, da Excel konverterte overskriftene til:
Når Wyn tok dataene inn i Power Query, åpnet han deretter rullegardinmenyen Bruk første rad som overskrifter og valgte Bruk overskrifter som første rad. Jeg skjønte aldri at dette var der. Det oppretter et trinn som heter Table.DemoteHeaders.
Men selv med Wyns forbedring, måtte han fremdeles senere trekke ut de to første tegnene fra disse overskriftene.
Den andre innovasjonen er MF Wongs teknikk. Da han opprettet bordet, fjernet han avkrysningen Mitt bord har overskrifter!
Dette sørger for at Excel lar flere Q1-overskrifter være alene, og det er ikke nødvendig å trekke ut det ekstra suffikset senere.
Jeg forstår at det er folk i leiren "Jeg elsker bord". MF Wongs video demonstrerte hvordan han kunne legge til nye ansatte til høyre for dataene, og tabellen utvides automatisk. Det er mange gode grunner til å bruke tabeller.
Men fordi jeg elsker delsummer, egendefinerte visninger og filtrer etter utvalg, pleier jeg ikke å bruke tabeller. Så jeg setter pris på løsningen fra Jason M. Han holdt dataene som det navngitte området UglyData. Så snart han importerte dataene til Power Query, slettet han disse to trinnene:
Nå, med dataene bare i rad 1, er det ikke noe problem å ha mange kolonner kalt Q1.
Her er Wyn Hopkins kode som 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å tilbake til hovedsiden for Podcast 2316-utfordringen.
Les neste artikkel i denne serien: Power Query: Delete this, Delete them, or delete nothing ?.
