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

Заметка

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

Хотя интерфейс Power Query очень мощный, те, кто может выйти за его рамки, обладают сверхспособностями. Фрэнк Тонсен прислал это решение. Возьмем точку, где данные выглядят так:

Данные в Power Query

Затем Фрэнк кодирует Table.Split, указывая, что каждые 5 записей должны быть новой таблицей.

Таблица.Split

Оттуда Table.FromList и многое другое. В этот момент вы можете щелкнуть любую ячейку таблицы и просмотреть данные в этой таблице. Вот вторая ячейка таблицы.

Table.FromList

Вот код Фрэнка:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), RemovedTotals = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), TransposedTable = Table.Transpose(RemovedTotals), PromotedHeaders = Table.PromoteHeaders(TransposedTable, (PromoteAllScalars=true)), SplittedTable = Table.Split(PromotedHeaders,5), TableFromList = Table.FromList(SplittedTable, Splitter.SplitByNothing(), null, null, ExtraValues.Error), AddedColumn1 = Table.AddColumn(TableFromList, "Column2", each Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders((Column1))))), AddedColumn2 = Table.AddColumn(AddedColumn1, "Column3", each Table.AddColumn((Column2), "Employee", (x) => Table.ColumnNames((Column2))(1))), AddedColumn3 = Table.AddColumn(AddedColumn2, "Column4", each Table.RenameColumns((Column3), (Table.ColumnNames((Column3))(1), "Total"))), Combined = Table.Combine(AddedColumn3(Column4)), ReorderedColumns = Table.ReorderColumns(Combined,("Category Description", "Employee", "Q1", "Q2", "Q3", "Q4", "Total")), ChangedType = Table.TransformColumnTypes(ReorderedColumns,(("Category Description", type text), ("Employee", type text), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", type number), ("Total", type number))) in ChangedType

Goodly from YouTube также поделился видео с пользовательским M-решением, которое динамически определяет все столбцы, начинающиеся с «Сотрудник». Хотя в реальной жизни это не сработает, когда сотрудников зовут Энди, Бетти, Чарли, это классное видео: https://www.youtube.com/watch?v=xamU5QLNiew.

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

Прочтите следующую статью из этой серии: Power Query: мир Билла Шиша.