Забележка
Това е една от поредицата статии, описващи подробно решения, изпратени за предизвикателството на Podcast 2316.
Поздрави на Fowmy за това решение. В реалния живот няма да работи, но работи добре с дадените фалшиви данни.
Във всяко решение има точка, в която трябва да разберете кои редове съдържат имена на служители и кои редове съдържат тримесечия.
Вместо да използва условна колона, Fowmy се разделя по разделител, използвайки Q като разделител. Вижте това … всички тримесечия вече са в нова колона.
По-горе, преди да можете да попълните, имате нужда от празните места в Име на служител, за да кажете Null. Fowmy решава това с:
= Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,("Employee Name"))
Ето защо това няма да работи с реални данни: Ще има служител с Q на тяхно име. Компанията ще наеме Angelique, Dominique или Ezequiel. Това ще обърка страхотното решение за разделяне на колони от Fowmy.
По-късно, когато Fowmy иска 1, 2, 3, 4 да каже Q1, Q2, Q3, Q4, използвайте Format, Add Prefix:
По-долу е кодът на Fowmy:
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"Джонатан Купър изпрати вариант в тази техника. Джонатан използва четири стъпки, за да промени „Q1“ на „_Q1“, след това „Q2“ на „_Q2“, след това „Q3“ на „_Q3“, след това „Q4“ на „_Q4“. Ето веднага след промяна на Q2:
По-късно, когато Джонатан се разделя от долната черта, той пречи на Куентин да се раздели. Той също така предотвратява по-късно да се налага да добавяте Q преди колоната.
Също така ми харесва, че Джонатан замени всички нули с нули в крайното решение. Ето неговия код:
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"Върнете се на главната страница за предизвикателството Podcast 2316.
Прочетете следващата статия от тази поредица: Сортиране на договорените покупки.
