Заметка
Это одна из серии статей, в которых подробно описаны решения, присланные для задачи Podcast 2316.
Одна из проблем с моим решением заключается в том, что окончательная последовательность категорий не обязательно соответствовала исходной последовательности столбцов. Я понял это в самом конце своего видео, и, поскольку это не было особо важным, я не беспокоился об этом.
Однако Джош Джонсон прислал решение, которое справилось с этим. Когда Джош сказал, что использует столбец индекса, я предположил, что это похоже на индекс и модуль в Power Query: числовые группы записей от 1 до 5 многократно. Но использование Джоша было совершенно другим.
Примечание. MVP по Excel Джон МакДугалл также использовал этот метод, но он добавил столбец индекса в конец описания категории. Посмотрите видео Джона здесь: https://www.youtube.com/watch?v=Dqmb6SEJDXI и узнайте больше о его коде здесь: Excel MVP атакуют проблему очистки данных в Power Query.
В начале процесса, когда у Джоша было всего шесть записей, он добавил индекс, начинающийся с 1. Джош щелкнул в строке формул и переименовал столбец «Индекс» в «Категория».
Столбец Категория был новым последним столбцом. Он использовал Move, to Beginning, чтобы переместить его первым:
После этого происходит множество других шагов. Это инновационные шаги, которые до сих пор в основном освещались в других статьях. После многих таких шагов я начал думать, что номера категорий от 1 до 6 были просто ошибкой. Я подумал, что, возможно, Джош собирался удалить их, не используя их.
Josh Unpivots, затем условный столбец, затем заполнение, затем поворот, складывание итогов. Кажется, он никогда не использует этот столбец категории. После многих шагов он здесь:
Но затем на последних шагах Джош сортирует данные по имени сотрудника, а затем по категории!
На этом этапе он может удалить столбец «Категория». Последнее отличие: PTO предшествует проекту A, как и в исходных колонках. Это приятный штрих.
Я также отмечу, что Джош прислал видео, на котором он выполняет эти шаги. Престижность Джошу за использование сочетаний клавиш внутри Power Query!
Вот код Джоша:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "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 Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Вернитесь на главную страницу испытания «Подкаст 2316».
Прочтите следующую статью из этой серии: MVP Excel решают проблему очистки данных в Power Query.
