Merk
Dette er en av en serie artikler som beskriver løsninger som er sendt inn for Podcast 2316-utfordringen.
Kudos til Fowmy for denne løsningen. Det fungerer ikke i det virkelige liv, men det fungerer fint med falske data gitt.
Det er et poeng i hver løsning hvor du må finne ut hvilke rader som inneholder ansattes navn og hvilke rader som inneholder kvartaler.
I stedet for å bruke en betinget kolonne, delte Fowmy etter skilletegn, og bruk Q som skilletegn. Sjekk dette… alle kvartalene er nå i en ny kolonne.
Før du kan fylle ut, trenger du tomt i Navn på ansatt for å si Null. Fowmy løser dette med:
= Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,("Employee Name"))
Dette er grunnen til at dette ikke fungerer med ekte data: Det kommer til å være en ansatt med en Q i navnet. Selskapet vil ansette Angelique, Dominique eller Ezequiel. Det vil skru opp den fantastiske Split Column-løsningen fra Fowmy.
Senere, når Fowmy vil at 1, 2, 3, 4 skal si Q1, Q2, Q3, Q4, bruk Format, Add Prefix:
Nedenfor er Fowmys kode:
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 sendte inn en variant på denne teknikken. Jonathan brukte fire trinn for å endre “Q1” til “_Q1”, deretter “Q2” til “_Q2”, deretter “Q3” til “_Q3” og deretter “Q4” til “_Q4”. Her er rett etter endring av 2. kvartal:
Senere, da Jonathan deler seg under understrekningen, forhindrer han at Quentin blir splittet. Han forhindrer også senere å måtte legge til Q før kolonnen.
Jeg liker også at Jonathan erstattet alle nuller med null i den endelige løsningen. Her er koden hans:
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"Gå tilbake til hovedsiden for Podcast 2316-utfordringen.
Les neste artikkel i denne serien: Sortering av ordrelinjer.
