Power Query: Справяне с множество еднакви заглавки - Съвети на Excel

Съдържание

Забележка

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

В моя оригинален проблем с оформянето на данни се натъкнах на проблем много рано в процеса. Входящите данни щяха да имат много колони със заглавие Q1.

Много колони

В моето решение създадох именен диапазон „UglyData“ и го импортирах в Power Query. Това води до нещастния резултат от Power Query, който преименува колоните ми на Q1_1.

Преименувани колони

По-късно, след депивация, трябваше да извлека само левите два знака от тези заглавки.

Имаше три отделни решения на този проблем:

  • Wyn Hopkins и Demote Headers
  • MF Wong и премахнете отметката от My Table Has Headers (също предложено от Peter Bartholomew)
  • Джейсън М и просто изтрийте Промотираните заглавки (също предложени от Ondřej Malinský и Excel MVP John MacDougall)

Първата иновация беше от Уин Хопкинс от Access Analytic. Вместо посочен диапазон, Wyn преобразува данните в таблица, използвайки Ctrl + T. В този момент е нанесена щета на заглавията, тъй като Excel преобразува заглавията в:

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

След като Wyn взе данните в Power Query, той отвори падащото меню Use First Row as Headers и избра Use Headers as First Row. Никога не съм осъзнавал, че това е там. Създава стъпка, наречена Table.DemoteHeaders.

Използвайте заглавки като първи ред

Но дори и с подобрението на Wyn, той все пак ще трябва да извлече първите 2 знака от тези заглавия.

Втората иновация е техниката на MF Wong. Когато създаде таблицата, той премахна отметката от My Table Has Headers!

Моята таблица има заглавки

Това гарантира, че 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"

Върнете се на главната страница за предизвикателството Podcast 2316.

Прочетете следващата статия от тази поредица: Power Query: Изтрийте това, изтрийте тези или не изтрийте нищо ?.

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