Poznámka
Toto je jeden zo série článkov s podrobnými riešeniami zaslanými k výzve Podcast 2316.
Pri riešení pretvárania údajov som chcel spôsob, ako zistiť, či stĺpec obsahuje meno zamestnanca alebo hodnotu ako Q1, Q2, Q3, Q4. Pri svojom riešení som predpokladal, že nikto nebude mať meno s 2 znakmi, a tak som pridal stĺpec na výpočet dĺžky textu v stĺpci.
Jason M sa vyhol potrebe stĺpca Dĺžka pridaním troch klauzúl Else If do svojho Podmieneného stĺpca.
Podmienený výpočet pre zamestnanca potom hľadá, aby Štvrťrok bol Null: if (Quarter) = null then (Popis kategórie) else null.
Tu je Jasonov M kód:
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ý poslal riešenie, ktoré používalo aj viac klauzúl Else If:
Matthew Wykle poslal riešenie s ešte ďalším spôsobom identifikácie štvrtí. Jeho metóda kontroluje, či text začína na Q a druhá číslica je menšia ako 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 použil tento vzorec na získanie Meno zamestnanca, Vyplnené a potom Filtrovaný stĺpec 1 tak, aby obsahoval iba Q1, Q2, Q3 alebo Q4. Túto metódu použil aj Oz Du Soleil.
Excel MVP Ken Puls pravdepodobne vyhral so svojím vzorcom. Hľadá sa podčiarknutie, aby sa zistilo, či to nie je meno zamestnanca.
Pozrite si úplné riešenie Kena na stránkach Excel MVP Attack the Data Cleansing Problem in Power Query.
Vráťte sa na hlavnú stránku výzvy Podcast 2316.
Prečítajte si nasledujúci článok v tejto sérii: Power Query: Riešenie viacerých identických hlavičiek.
