Композитно решение за подкаст 2316 Предизвикателство - Съвети за Excel

Забележка

Това е една от поредицата статии, описващи подробно решения, изпратени за предизвикателството на Podcast 2316.

След като изучих всички идеи, изпратени от зрителите, избрах любимите си техники от всеки видеоклип. Последното ми решение използва следните стъпки:

• Вземете данни от именуван диапазон
• Изтрийте двете допълнителни стъпки, добавени към Популяризиране на заглавки и промяна на типа. Това предотвратява необходимостта от разбиване на суфикса от четвъртинките. Благодаря на Джейсън М, Ondřej Malinský и Peter Bartholomew за тази идея.
• Транспониране
• Популяризиране на хедъри
• Премахване, Топ редове, Топ 5 реда. Хубав трик от MF Wong.
• Заменете Q1 с _Q1. Повторете за останалите три четвърти. Благодаря Джонатан Купър.
• Разделено от разделител в _. Тази невероятна стъпка запазва имената в една колона и премества четвъртинките в следващата колона. Предложен от Фоуми, усъвършенстван от Джонатан Купър.
• (Нито стъпка!) Посегнете в лентата с формули и преименувайте колоните на Служител и Квартал. Благодаря Джош Джонсън
• В колоната Служител не замествайте нищо с null
• Попълнете
• В колоната Quarter променете null на Total. Тази идея от Майкъл Карпфен
• Депилиране на други колони. Преименувайте Attrib в Категория в лентата с формули
• Осеви квартали
• Преместване на общата колона до края

Ето последния ми код:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Removed Top Rows" = Table.Skip(#"Changed Type",5), #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","Q1","_Q1",Replacer.ReplaceText,("Category Description")), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Q2","_Q2",Replacer.ReplaceText,("Category Description")), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Q3","_Q3",Replacer.ReplaceText,("Category Description")), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Q4","_Q4",Replacer.ReplaceText,("Category Description")), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3", "Category Description", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), ("Employee", "Qtr")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Employee", type text), ("Qtr", type text))), #"Replaced Value4" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,("Employee")), #"Filled Down" = Table.FillDown(#"Replaced Value4",("Employee")), #"Replaced Value5" = Table.ReplaceValue(#"Filled Down",null,"Total",Replacer.ReplaceValue,("Qtr")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value5", ("Employee", "Qtr"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(Qtr)), "Qtr", "Value", List.Sum), #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",("Employee", "Attribute", "Q1", "Q2", "Q3", "Q4", "Total")) in #"Reordered Columns"

Всички, споменати в тези статии или видеоклипове, печелят корекция на Excel Guru. Вече изпратих няколко по пощата. Ако не получите такъв, оставете коментар на видеото по-долу.