Примітка
Це одна із серії статей, що детально описують рішення, спрямовані на виклик Podcast 2316.
У своїй оригінальній проблемі формування даних я дуже рано стикався з проблемою. Вхідні дані мали мати багато стовпців із заголовком Q1.
У своєму рішенні я створив іменований діапазон “UglyData” та імпортував його до Power Query. Це призвело до нещасного результату Power Query, який перейменував мої стовпці на Q1_1.
Пізніше, після вилучення, мені довелося витягти з цих заголовків лише ліві два символи.
Існували три окремі рішення цієї проблеми:
- Вин Хопкінс і Пониження заголовків
- MF Wong і зніміть прапорець My Table Has Headers (також запропоновано Пітером Варфоломієм)
- Джейсон М і просто видаліть рекламовані заголовки (також запропоновані Ondřej Malinský та Excel MVP John MacDougall)
Перше нововведення було зроблено Вайн Хопкінс із Access Analytic. Замість названого діапазону Wyn перетворив дані в таблицю, використовуючи Ctrl + T. На цьому етапі було завдано шкоди заголовкам, оскільки Excel перетворив заголовки на:
Після того, як Wyn взяв дані до Power Query, він відкрив спадне меню Використовувати перший рядок як заголовки та вибрав Використовувати заголовки як перший рядок. Я ніколи не розумів, що це там. Він створює крок під назвою Table.DemoteHeaders.
Але навіть з вдосконаленням Вина йому пізніше доведеться витягти перші 2 символи з цих заголовків.
Друге нововведення - техніка М. Ф. Вонга. Коли він створював таблицю, він зняв прапорець "Моя таблиця має заголовки"!
Це гарантує, що Excel залишає кілька заголовків Q1 у спокої, і немає потреби витягувати зайвий суфікс пізніше.
Я розумію, що в таборі “Я люблю столи” є люди. Відео М. Ф. Вонга продемонструвало, як він може додати нових співробітників праворуч від даних, і таблиця автоматично розширюється. Існує маса вагомих причин використовувати таблиці.
Але, оскільки я люблю проміжні підсумки, власні подання та фільтр за виділенням, я, як правило, не використовую таблиці. Отже, я ціную рішення Джейсона М. Він зберігав дані як названий діапазон UglyData. Як тільки він імпортував дані до Power Query, він видалив ці два кроки:
Тепер, маючи дані просто в рядку 1, немає жодних клопотів, що мають багато стовпців під назвою 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: Видалити це, Видалити ці або нічого не видалити ?.
