У меня есть электронная таблица, которая содержит имена столбцов, такие как название продукта, количество, стоимость.
Я хочу преобразовать это в строки данных, которые содержат название продукта, количество, стоимость.
См. Изображение ниже, чтобы узнать, что я хочу.
Как лучше всего справиться с этим в Power Query M Language?
Не уверены, хочу ли я развернуть только столбцы с названием продукта, количеством и стоимостью?
Спасибо
Вот способ ...
Начиная с этой таблицы как Table1:
Вы можете выбрать столбец «Клиент» и «Отменить сводку других столбцов», чтобы получить следующее:
Затем вы можете добавить столбец индекса (оставьте его с именем Index), а затем также настраиваемый столбец (оставьте его с именем Custom) с if Text.EndsWith([Attribute],"Cost") then 1 else 0 в качестве формулы, чтобы получить следующее:
Затем добавьте еще один настраиваемый столбец ... Назовите его Общая стоимость и введите #"Unpivoted Other Columns"[Value]{[Index]+(List.Count(#"Added Custom"[Custom])/List.Sum(#"Added Custom"[Custom]))} в качестве формулы, чтобы получить:
Два вышеуказанных шага были, во-первых, для настройки определения соответствующей стоимости футболок на основе положения стоимости в столбце «Значение», а затем для фактического определения стоимости и записи ее в той же строке, что и соответствующие футболки. Столбец «Индекс» предоставляет информацию о расположении строк, а столбец «Пользовательский» предоставляет информацию о количестве - как общее количество в списке, так и количество строк с указанием стоимости. Я использую информацию о счетчике, чтобы определить, на сколько позиций индекса нужно переместиться вниз по столбцу «Значение» для динамического получения связанных значений затрат.
Затем отфильтруйте столбец «Атрибут» с помощью «Текстовые фильтры»> «Не заканчивается на ...» и введите слово «Стоимость». Все строки с записью атрибута, заканчивающейся словом Cost, должны исчезнуть:
Удалите столбцы Index и Custom и переименуйте столбцы Attribute и Value в Product Name и Quantity соответственно, чтобы получить окончательный результат:
Вот мой M-код:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.EndsWith([Attribute],"Cost") then 1 else 0),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Total Cost", each #"Unpivoted Other Columns"[Value]{[Index]+(List.Count(#"Added Custom"[Custom])/List.Sum(#"Added Custom"[Custom]))}),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each not Text.EndsWith([Attribute], "Cost")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Product Name"}, {"Value", "Quantity"}})
in
#"Renamed Columns"
Ключевым моментом здесь является поворот и отключение.
Начиная с такой таблицы,
Выберите четыре правых столбца и нажмите Transform> Unpivot Columns, чтобы получить эту таблицу:
Теперь создайте настраиваемый столбец, который классифицирует значение по этой формуле.
if Text.EndsWith([Attribute], "Cost") then "Cost" else "Quantity"
Я также отрезал " Cost" кусок в конце столбца Attribute. Вы можете либо Преобразовать> Заменить значения и заменить " Cost" ничем, либо Преобразовать> Извлечь> Текст перед разделителем " Cost".
Теперь поверните настраиваемый столбец (выберите столбец Value в качестве столбца значений) и, наконец, переименуйте столбец Attribute в Product Name.
Вот мой M-код для всех шагов:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknNK0stUnAuLS7Jz00tUtJRMjIGEoYmIJapqZ6pAYhnZKpnYKAUqxOt5JyRmZyYno+swdAQSJiagtUZgNQBeeYQDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Product Orange T-shirt" = _t, #"Product Blue T-shirt" = _t, #"Product Orange T-shirt Cost" = _t, #"Product Blue T-shirt Cost" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product Orange T-shirt", Int64.Type}, {"Product Blue T-shirt", Int64.Type}, {"Product Orange T-shirt Cost", type number}, {"Product Blue T-shirt Cost", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Text.EndsWith([Attribute], "Cost") then "Cost" else "Quantity"),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom"," Cost","",Replacer.ReplaceText,{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Custom]), "Custom", "Value", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Product Name"}})
in
#"Renamed Columns"
