Power Query: работа с несколькими идентичными заголовками - Советы по Excel

Содержание

Заметка

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

В моей исходной задаче формирования данных я столкнулся с проблемой очень рано. Входящие данные должны были содержать много столбцов с заголовком Q1.

Многие столбцы

В своем решении я создал именованный диапазон «UglyData» и импортировал его в Power Query. Это привело к неприятному результату, когда Power Query переименовал мои столбцы в Q1_1.

Переименованные столбцы

Позже, после отмены поворота, мне пришлось извлечь только два левых символа из этих заголовков.

У этой проблемы было три отдельных решения:

  • Вин Хопкинс и заголовки понижения
  • MF Wong и снимите флажок My Table Has Headers (также предложено Питером Бартоломью)
  • Jason M и просто удалите Promoted Headers (также предложено Ондржем Малинским и MVP Excel Джоном МакДугаллом)

Первое нововведение было от Вина Хопкинса из Access Analytic. Вместо именованного диапазона Wyn преобразовал данные в таблицу с помощью Ctrl + T. На этом этапе заголовки были повреждены, поскольку Excel преобразовал заголовки в:

Преобразовано в таблицу: Ctrl + T

После того, как Вин перенес данные в Power Query, он открыл раскрывающееся меню «Использовать первую строку в качестве заголовков» и выбрал «Использовать заголовки в качестве первой строки». Я никогда не понимал, что это было там. Он создает шаг под названием Table.DemoteHeaders.

Использовать заголовки как первую строку

Но даже с улучшением Вина ему все равно придется извлекать первые 2 символа из этих заголовков.

Второе нововведение - это методика М.Ф. Вонга. При создании таблицы он снял флажок "Моя таблица имеет заголовки!"

В моей таблице есть заголовки

Это гарантирует, что Excel оставит несколько заголовков Q1 в покое и нет необходимости извлекать дополнительный суффикс позже.

Несколько заголовков Q1

Насколько я понимаю, в лагере «Я люблю столы» есть люди. Видео MF Wong продемонстрировало, как он может добавлять новых сотрудников справа от данных, и таблица автоматически расширяется. Есть много веских причин использовать таблицы.

Но поскольку мне нравятся промежуточные итоги, настраиваемые представления и фильтр по выделению, я не использую таблицы. Итак, я ценю решение от Джейсона М. Он сохранил данные как именованный диапазон UglyData. Как только он импортировал данные в Power Query, он удалил эти два шага:

Удаленные шаги

Теперь, когда данные просто помещены в строку 1, нет проблем с наличием множества столбцов с именем Q1.

Многие столбцы Q1

Вот код Вина Хопкина, показывающий DemotedHeaders:

let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"

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

Прочтите следующую статью этой серии: Power Query: удалить это, удалить их или ничего не удалять ?.

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