Poznámka
Toto je jeden zo série článkov s podrobnými riešeniami zaslanými k výzve Podcast 2316.
Sláva Fowmymu za toto riešenie. V skutočnom živote to nebude fungovať, ale s uvedenými falošnými údajmi funguje pekne.
V každom riešení je bod, v ktorom musíte zistiť, ktoré riadky obsahujú mená zamestnancov a ktoré riadky štvrtiny.
Namiesto použitia podmieneného stĺpca sa Fowmy rozdelí oddeľovačom a ako oddeľovač sa použije Q. Vyskúšajte to … všetky štvrtiny sú teraz v novom stĺpci.
Vo vyššie uvedenom texte musíte predtým, ako budete môcť vyplniť, prázdne miesta v poli Meno zamestnanca uviesť Null. Fowmy to rieši pomocou:
= Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,("Employee Name"))
Tu je dôvod, prečo to nebude fungovať so skutočnými údajmi: Bude existovať zamestnanec s Q v jeho mene. Spoločnosť zamestná Angelique, Dominique alebo Ezequiel. To zvrhne úžasné riešenie Split Column od Fowmy.
Neskôr, keď chce Fowmy 1, 2, 3, 4 povedať Q1, Q2, Q3, Q4, použite Format, Add Prefix:
Nižšie je uvedený Fowmyho kód:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByEachDelimiter(("Q"), QuoteStyle.Csv, false), ("Employee Name", "Qtr")), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,("Employee Name")), #"Filled Down" = Table.FillDown(#"Replaced Value",("Employee Name")), #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", (PromoteAllScalars=true)), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ((Category Description) "Dept. Total") and ((Column2) null)), #"Added Prefix" = Table.TransformColumns(#"Filtered Rows", (("Column2", each "Q" & Text.From(_, "en-US"), type text))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Prefix", ("Category Description", "Column2"), "Attribute", "Value"), #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",("Attribute", "Category Description", "Column2", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"(Column2)), "Column2", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",(("Category Description", "Employee Name"), ("Attribute", "Category Description"))), #"Sorted Rows" = Table.Sort(#"Renamed Columns",(("Employee Name", Order.Ascending), ("Category Description", Order.Ascending))) in #"Sorted Rows"Jonathan Cooper poslal variáciu na túto techniku. Jonathan použil štyri kroky na zmenu „Q1“ na „_Q1“, potom „Q2“ na „_Q2“, potom „Q3“ na „_Q3“ a potom „Q4“ na „_Q4“. Tu je hneď po zmene Q2:
Neskôr, keď sa Jonathan rozdelí podčiarkovníkom, zabráni Quentinovi v rozdelení. Tiež zabráni tomu, aby musel neskôr pred stĺpec pridať písmeno Q.
Tiež sa mi páči, že Jonathan v konečnom riešení nahradil všetky nuly nulami. Tu je jeho kód:
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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"replace 0 with null" = Table.ReplaceValue(#"Unpivoted Other Columns",0,null,Replacer.ReplaceValue,("Value")), #"prep qtr to be split1" = Table.ReplaceValue(#"replace 0 with null","Q1","_Q1",Replacer.ReplaceText,("Attribute")), #"prep qtr to be split2" = Table.ReplaceValue(#"prep qtr to be split1","Q2","_Q2",Replacer.ReplaceText,("Attribute")), #"prep qtr to be split3" = Table.ReplaceValue(#"prep qtr to be split2","Q3","_Q3",Replacer.ReplaceText,("Attribute")), #"prep qtr to be split4" = Table.ReplaceValue(#"prep qtr to be split3","Q4","_Q4",Replacer.ReplaceText,("Attribute")), #"Split Column by underscore" = Table.SplitColumn(#"prep qtr to be split4", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), ("Employee", "Period", "Attribute.3")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by underscore",(("Employee", type text), ("Period", type text), ("Attribute.3", Int64.Type))), #"prep to fill down" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,("Employee")), #"Filled Down employee" = Table.FillDown(#"prep to fill down",("Employee")), #"remove dept totals" = Table.SelectRows(#"Filled Down employee", each (Employee) "Dept. Total"), #"remove employee totals" = Table.SelectRows(#"remove dept totals", each (Period) null), #"Removed extra column" = Table.RemoveColumns(#"remove employee totals",("Attribute.3")), #"Pivoted Column" = Table.Pivot(#"Removed extra column", List.Distinct(#"Removed extra column"(Period)), "Period", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee", Order.Ascending), ("Category Description", Order.Ascending))) in #"Sorted Rows"Vráťte sa na hlavnú stránku výzvy Podcast 2316.
Prečítajte si nasledujúci článok v tejto sérii: Zoradenie riadkových položiek.
