Opmerking
Dit is een van een reeks artikelen over oplossingen die zijn ingezonden voor de Podcast 2316-uitdaging.
Excel MVP Oz Du Soleil van het Excel on Fire-kanaal op YouTube noemde de Braziliaanse Bull Rider Kaique Pachecho. Oz was de eerste die opmerkte dat ik de langzame weg ging om de vier kwartalen toe te voegen.
Oz's video is:
https://www.youtube.com/watch?v=OluZlF44PNI
Zijn code is:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"Een andere oplossing, deze van Excel MVP John MacDougall.
- John was de eerste die zei dat door het verwijderen van de twee extra stappen die Power Query heeft toegevoegd, je de oneven achtervoegsels op de dubbele Q1 Q2 Q3 Q4-koppen verwijdert.
- John gebruikte vroeg een Index-kolom die aan het einde zou worden gebruikt om te sorteren. Maar - John heeft zijn indexkolom samengevoegd na de categoriebeschrijving. Hij gebruikte een verticaal pijpkarakter | zodat hij de gegevens later kon doorbreken.
- John typte zijn voorwaardelijke kolom als een aangepaste kolom in plaats van de interface voor voorwaardelijke kolommen te gebruiken.
Bekijk de video van John hier:
https://www.youtube.com/watch?v=Dqmb6SEJDXI
Excel MVP Ken Puls, co-auteur van de M is voor (Data) Monkey boek verzonden in drie oplossingen. Zijn voorwaardelijke column is waarschijnlijk de kortste.
Maar Ken's voorkeursoplossing negeert de oorspronkelijke vraag. In plaats van de tabel in Power Query te maken, maakt hij een draaibare gegevensset in Power Query en eindigt hij met een draaitabel.
Ken's laatste voorbeeld in Power Query ziet er als volgt uit:
Hier is de code van Ken:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"Nadat hij deze query alleen als verbinding heeft gemaakt, gebruikt hij een draaitabel om het eindrapport te maken.
Oplossingen van andere MVP's:
- De Wyn Hopkins-code is hier: Power Query: omgaan met meerdere identieke headers.
- De code van Mike Girvin is hier: Power Query: 2 links uit een kolom extraheren.
- De formule-oplossing van Roger Govier is hier: Formula Solutions.
Keer terug naar de hoofdpagina voor de Podcast 2316-uitdaging.
Lees het volgende artikel in deze serie: Power Query: Beyond the User Interface: Table.Split en meer.
