Opmerking
Dit is een van een reeks artikelen over oplossingen die zijn ingezonden voor de Podcast 2316-uitdaging.
In mijn oplossing om de gegevens opnieuw vorm te geven, wilde ik een manier om te zien of een kolom de naam van een werknemer of een waarde zoals Q1, Q2, Q3, Q4 bevatte. In mijn oplossing ging ik ervan uit dat niemand een naam met 2 tekens zou hebben, en daarom heb ik een kolom toegevoegd om de lengte van de tekst in de kolom te berekenen.
Jason M vermeed de noodzaak van de kolom Lengte door drie Else If-clausules toe te voegen aan zijn Conditional Column.
De voorwaardelijke berekening voor Werknemer zoekt dan dat Kwartaal Null is: if (Kwartaal) = null dan (Categoriebeschrijving) anders nul.
Hier is de M-code van Jason:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "Quarter", each if (Category Description) = "Q1" then (Category Description) else if (Category Description) = "Q2" then (Category Description) else if (Category Description) = "Q3" then (Category Description) else if (Category Description) = "Q4" then (Category Description) else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Employee", each if (Quarter) = null then (Category Description) else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column1",("Quarter")), #"Filled Down" = Table.FillDown(#"Filled Up",("Employee")), #"Inserted Distinct Count" = Table.AddColumn(#"Filled Down", "Distinct Count", each List.NonNullCount(List.Distinct(((Category Description), (Employee)))), Int64.Type), #"Filtered Rows1" = Table.SelectRows(#"Inserted Distinct Count", each (Distinct Count) 1), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Distinct Count")), #"Removed Columns" = Table.RemoveColumns(#"Removed Columns1",("Category Description")), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ((Employee) "Dept. Total")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", ("Quarter", "Employee"), "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",(("Attribute", "Category Description"))), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"(Quarter)), "Quarter", "Value"), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Reordered Columns" = Table.ReorderColumns(#"Inserted Sum",("Category Description", "Employee", "Q1", "Q2", "Q3", "Q4", "Total")) in #"Reordered Columns"Ondřej Malinský stuurde een oplossing die ook meerdere Else If-clausules gebruikte:
Matthew Wykle stuurde een oplossing met nog een andere manier om de wijken te identificeren. Zijn methode controleert of de tekst begint met Q en dat het tweede cijfer kleiner is dan 5:
if Text.Start((Attribute),1)="Q" and Number.From(Text.Middle((Attribute),1,1))<5 then Text.Start((Attribute),2) else "Total")
Christian Neuberger heeft deze formule gebruikt om Werknemersnaam, Ingevuld en vervolgens Gefilterd kolom 1 te krijgen om alleen Q1, Q2, Q3 of Q4 op te nemen. Oz Du Soleil gebruikte deze methode ook.
Excel MVP Ken Puls wint waarschijnlijk met zijn formule. Het zoekt naar een onderstrepingsteken om te weten of dit niet de naam van de werknemer is.
Bekijk de volledige oplossing van Ken op Excel MVP's Attack the Data Cleansing Problem in Power Query.
Keer terug naar de hoofdpagina voor de Podcast 2316-uitdaging.
Lees het volgende artikel in deze serie: Power Query: omgaan met meerdere identieke headers.
