Piezīme
Šis ir viens no rakstu sērijas, kurā sīki aprakstīti Podcast 2316 izaicinājumam nosūtītie risinājumi.
Kudos Fowmy par šo risinājumu. Tas nedarbosies reālajā dzīvē, bet tas labi darbojas ar sniegtajiem viltus datiem.
Katrā risinājumā ir punkts, kurā jānoskaidro, kurās rindās ir darbinieku vārdi un kurās - ceturtdaļas.
Tā vietā, lai izmantotu nosacītu kolonnu, Fowmy dala pēc atdalītāja, kā atdalītāju izmantojot Q. Pārbaudiet to … visi ceturkšņi tagad atrodas jaunā kolonnā.
Iepriekš norādītajā gadījumā, pirms varat aizpildīt tekstu, jums ir nepieciešami lauki Darbinieka vārdā, lai teiktu Null. Fowmy to atrisina ar:
= Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,("Employee Name"))
Lūk, kāpēc tas nedarbosies ar reāliem datiem: Būs darbinieks ar Q uz viņu vārda. Uzņēmums pieņems darbā Angelique, Dominiku vai Ezequiel. Tas ieskrūvēs Fowmy lielisko Split Column risinājumu.
Vēlāk, kad Fowmy vēlas, lai 1, 2, 3, 4 teiktu Q1, Q2, Q3, Q4, izmantojiet formātu, pievienojiet prefiksu:
Zemāk ir Fowmy kods:
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"Džonatans Kūpers iesūtīja šīs tehnikas variāciju. Džonatans izmantoja četras darbības, lai mainītu “Q1” uz “_Q1”, pēc tam “Q2” uz “_Q2”, tad “Q3” uz “_Q3”, pēc tam “Q4” uz “_Q4”. Šeit ir uzreiz pēc Q2 maiņas:
Vēlāk, kad Džonatans dalās pa pasvītrojumu, viņš neļauj Kventinam šķelties. Viņš arī neļauj vēlāk pievienot Q pirms kolonnas.
Man arī patīk, ka Džonatans galīgajā risinājumā visas nulles aizstāja ar nulles vērtībām. Šeit ir viņa kods:
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"Atgriezieties uz Podcast 2316 izaicinājuma galveno lapu.
Izlasiet nākamo šīs sērijas rakstu: Rindu vienību kārtošana.
