Как отформатировать таблицу Excel, содержащую двойные заголовки, в Power Query
У меня есть таблица следующего формата:
NB. Следующий пример представляет собой сокращенную версию таблицы, в которой в качестве примера представлена только одна категория. В реальной таблице названия категорий перечислены в верхней строке, а месяцы года (с января 2021 г. по декабрь 2022 г.) — в строке ниже. Каждая последующая строка или столбец показывает значения за каждый месяц. В первой таблице ниже столбцы A, B и C представляют собой необходимую дополнительную информацию. Однако все, начиная со столбца D, представляет собой раздел таблицы с двойными заголовками и категориальной информацией.
Я пытаюсь сделать некоторые преобразования для достижения следующего результата:
Я пытался с помощью поворота, транспонирования и т. д., но я не смог достичь желаемого результата. Я новичок в использовании кода power query/M, но я не могу найти что-то подходящее, чтобы помочь мне достичь желаемого результата.
Ответы 2
Это должно делать то, что вы хотите. «Хитрость» заключается в том, чтобы развернуть столбцы «Дата».
Примечание. Поскольку вы показываете только одну категорию, было неясно, как она будет распространяться, поэтому я проигнорировал ее. Вам нужно будет изменить код, чтобы лучше учитывать изменения. Я подозреваю, что вам нужно будет сделать Table.Group для каждой категории, а затем вывести категорию в виде одного столбца в дополнение к приведенному ниже коду.
let
//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
//Remove top row and promote next row to headers
#"Removed Top Rows" = Table.Skip(Source,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
//Unpivot the "Date" columns
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers",
{"Parent/ Child Name", "ID", "Parent/ Child"}, "Attribute", "Value"),
//Set data type of Date columns to datetime
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type datetime}}),
//Sort ascending by Date and then by ID
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Attribute", Order.Ascending}, {"ID", Order.Ascending}}),
//Add Custom column for Year and Month
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Year", each Date.Year([Attribute]), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each DateTime.ToText([Attribute], "MMM"), type text),
//Remove unneeded columns and set desired order of remaining columns
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}),
#"Reordered Columns"= Table.ReorderColumns(#"Removed Columns",
{"Parent/ Child Name", "ID", "Parent/ Child", "Year", "Month", "Value"}),
//Set all data types
#"Set Types" = Table.TransformColumnTypes(#"Reordered Columns",
List.Zip({Table.ColumnNames(#"Reordered Columns"), {type text, Int64.Type, type text, Int64.Type, type text, Int64.Type}}))
in
#"Set Types"
@SahilParuk Ваше описание категории недостаточно конкретно, чтобы я мог действовать. И ваш пример включает только одну категорию. Если вы не можете понять, как с этим бороться, я предлагаю вам отредактировать свой вопрос, чтобы предоставить более реалистичный пример. Что касается вашего основного вопроса, это противоположно тому, что вы пишете.
Мои извинения. Я отредактировал описание своего вопроса. Надеюсь, это поможет. Я не смог использовать ваше решение, потому что изменились требования к данным. Я ценю помощь, хотя.
@SahilParuk Я полагаю, вы имеете в виду, что в вашем примере, где у вас есть ячейки, содержащие Col4 и ColN, это должны быть категории. Если это так, мы можем просто извлечь список категорий и добавить его в виде столбца. Но поскольку вы уже приняли ответ, я не буду сейчас беспокоиться.
Настоящим я отправляю вам M-коды, которые включают столбец [категория].
//изменить источник данных с заменой в первом исходном предложении ниже
let
Source = Excel.Workbook(File.Contents("C:\Users\zhenger\Desktop\powerquery sample.xlsx"), null, true),
table_Sheet = Source{[Item = "table",Kind = "Sheet"]}[Data],
#"Transposed Table" = Table.Transpose(table_Sheet),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each "-"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Column1", "Custom", "Column2", "Column3", "Column4", "Column5"}),
#"Inserted Merged Column" = Table.AddColumn(#"Reordered Columns", "Merged", each Text.Combine({[Column1], [Custom], Text.From([Column2], "zh-CN")}, ""), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Column1", "Custom", "Column2"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Merged", "Column3", "Column4", "Column5"}),
#"Transposed Table1" = Table.Transpose(#"Reordered Columns1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"-Parent/ Child Name", type text}, {"-ID", Int64.Type}, {"-Parent/ Child", type text}, {"Category 1-2021/1/1", Int64.Type}, {"Category 1-2021/2/1", Int64.Type}, {"Category 1-2021/3/1", Int64.Type}, {"Category 1-2021/4/1", Int64.Type}, {"Category 1-2021/5/1", Int64.Type}, {"Category 1-2021/6/1", Int64.Type}, {"Category 1-2021/7/1", Int64.Type}, {"Category 1-2021/8/1", Int64.Type}, {"Category 1-2021/9/1", Int64.Type}, {"Category 1-2021/10/1", Int64.Type}, {"Category 1-2021/11/1", Int64.Type}, {"Category 1-2021/12/1", Int64.Type}, {"Category 1-2022/1/1", Int64.Type}, {"Category 1-2022/2/1", Int64.Type}, {"Category 1-2022/3/1", Int64.Type}, {"Category 1-2022/4/1", Int64.Type}, {"Category 1-2022/5/1", Int64.Type}, {"Category 1-2022/6/1", Int64.Type}, {"Category 1-2022/7/1", Int64.Type}, {"Category 1-2022/8/1", Int64.Type}, {"Category 1-2022/9/1", Int64.Type}, {"Category 1-2022/10/1", Int64.Type}, {"Category 1-2022/11/1", Int64.Type}, {"Category 1-2022/12/1", Int64.Type}, {"Category 2-2021/1/1", Int64.Type}, {"Category 2-2021/2/1", Int64.Type}, {"Category 2-2021/3/1", Int64.Type}, {"Category 2-2021/4/1", Int64.Type}, {"Category 2-2021/5/1", Int64.Type}, {"Category 2-2021/6/1", Int64.Type}, {"Category 2-2021/7/1", Int64.Type}, {"Category 2-2021/8/1", Int64.Type}, {"Category 2-2021/9/1", Int64.Type}, {"Category 2-2021/10/1", Int64.Type}, {"Category 2-2021/11/1", Int64.Type}, {"Category 2-2021/12/1", Int64.Type}, {"Category 2-2022/1/1", Int64.Type}, {"Category 2-2022/2/1", Int64.Type}, {"Category 2-2022/3/1", Int64.Type}, {"Category 2-2022/4/1", Int64.Type}, {"Category 2-2022/5/1", Int64.Type}, {"Category 2-2022/6/1", Int64.Type}, {"Category 2-2022/7/1", Int64.Type}, {"Category 2-2022/8/1", Int64.Type}, {"Category 2-2022/9/1", Int64.Type}, {"Category 2-2022/10/1", Int64.Type}, {"Category 2-2022/11/1", Int64.Type}, {"Category 2-2022/12/1", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"-Parent/ Child Name", "-ID", "-Parent/ Child"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Category"}, {"Attribute.2", "date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([date]), Int64.Type),
#"Reordered Columns2" = Table.ReorderColumns(#"Inserted Month",{"-Parent/ Child Name", "-ID", "-Parent/ Child", "Category", "date", "Year", "Month", "Value"})
in
#"Reordered Columns2"
Спасибо, я попробую это. Когда вы говорите развернуть столбцы даты, вы имеете в виду выбрать все столбцы, у которых есть дата, а затем сказать развернуть другие столбцы? Некоторая ясность по категориям. Существует несколько категорий, и каждая категория имеет значения для каждого месяца года на 2021 и 2022 годы.