Uwaga
Jest to jeden z serii artykułów opisujących szczegółowo rozwiązania przesłane w ramach wyzwania Podcast 2316.
W moim rozwiązaniu polegającym na zmianie kształtu danych chciałem sprawdzić, czy kolumna zawiera imię i nazwisko pracownika, czy wartość, taką jak Q1, Q2, Q3, Q4. W moim rozwiązaniu założyłem, że nikt nie będzie miał nazwy składającej się z 2 znaków, dlatego dodałem kolumnę do obliczenia długości tekstu w kolumnie.
Jason M uniknął potrzeby stosowania kolumny Długość, dodając trzy klauzule Else If do swojej kolumny warunkowej.
Obliczenie warunkowe dla pracownika szuka następnie kwartału jako zerowego: jeśli (kwartał) = null, to (opis kategorii) else null.
Oto kod M Jasona:
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ý przesłał rozwiązanie, które wykorzystywało również wiele klauzul Else If:
Matthew Wykle przysłał rozwiązanie z jeszcze innym sposobem identyfikacji kwater. Jego metoda sprawdza, czy tekst zaczyna się od Q, a druga cyfra jest mniejsza niż 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 użył tej formuły, aby uzyskać nazwisko pracownika, wypełnione, a następnie przefiltrowaną kolumnę 1, aby uwzględnić tylko kwartał 1, 2, 3 lub 4. Oz Du Soleil również stosował tę metodę.
MVP Excela Ken Puls prawdopodobnie wygrywa ze swoją formułą. Szuka podkreślenia, aby wiedzieć, czy to nie jest nazwisko pracownika.
Zobacz pełne rozwiązanie Kena w witrynie Excel MVPs Attack the Data Cleansing Problem in Power Query.
Wróć do strony głównej wyzwania Podcast 2316.
Przeczytaj następny artykuł z tej serii: Dodatek Power Query: radzenie sobie z wieloma identycznymi nagłówkami.
