Excel MVP решает проблему очистки данных в Power Query - Советы по Excel

Содержание

Заметка

Это одна из серии статей, в которых подробно описаны решения, присланные для задачи Podcast 2316.

MVP Excel Оз Дю Солей из канала Excel on Fire на YouTube упомянул бразильского всадника на быках Кайке Пачечо. Оз был первым, кто заметил, что я пошел медленным путем, добавляя четыре четверти.

Видео Оза:
https://www.youtube.com/watch?v=OluZlF44PNI.

Его код:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"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))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

Еще одно решение, разработанное Джоном МакДугаллом, MVP по Excel.

  • Джон был первым, кто сказал, что, удаляя два дополнительных шага, добавленных Power Query, вы устраняете нечетные суффиксы в повторяющихся заголовках Q1 Q2 Q3 Q4.
  • Джон раньше использовал столбец индекса, который будет использоваться в конце для сортировки. Но … Джон объединил свой столбец индекса после описания категории. Он использовал символ вертикальной трубы | чтобы он мог позже разбить данные.
  • Джон ввел свой условный столбец как настраиваемый столбец вместо использования интерфейса условного столбца.
Условный столбец как настраиваемый столбец

Посмотреть видео Джона можно здесь:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

MVP Excel Кен Пулс, соавтор книги M для (Data) Monkey, отправлено в трех решениях. Его условная колонка, наверное, самая короткая.

Но предпочтительное решение Кена игнорирует исходный вопрос. Вместо создания таблицы в Power Query он создает сводный набор данных в Power Query, а затем завершает создание сводной таблицы.

Последний предварительный просмотр Кена в 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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Создав этот запрос только как соединение, он затем использует сводную таблицу для создания окончательного отчета.

Итоговый отчет со сводной таблицей

Решения от других MVP:

  • Код Вина Хопкинса находится здесь: Power Query: работа с несколькими идентичными заголовками.
  • Код Майка Гирвина находится здесь: Power Query: извлечение двух левых символов из столбца.
  • Решение формулы Роджера Говье находится здесь: Решения формулы.

Вернитесь на главную страницу испытания «Подкаст 2316».

Прочтите следующую статью этой серии: Power Query: Помимо пользовательского интерфейса: разделение таблиц и многое другое.

Интересные статьи...