#powerbi #powerquery #m
#powerbi #запрос мощности #m
Вопрос:
Я работаю с опросом американского сообщества в Power BI и хочу заменить значения в каждом столбце соответствующими значениями в другой таблице меток ответов. Например, раса кодируется как 1,2,3,4,5, но я хочу заменить ее на азиатскую, черную, родную и т.д. В соответствии с предоставленными метками ответов.
Допустим, у меня есть следующая таблица с тремя переменными и ответами людей:
| Переменная 1 | переменная 2 | переменная 3 |
|---|---|---|
| 1 | 2 | 3 |
| 2 | 3 | 2 |
| 3 | 1 | 1 |
| 3 | 3 | 2 |
| 2 | 2 | 3 |
Мне предоставлена эта таблица ключей ответа:
| ВарНаме | Клавиша ответа | Метка ответа |
|---|---|---|
| Переменная 1 | 1 | Синий |
| Переменная 1 | 2 | красный |
| Переменная 1 | 3 | Зеленый |
| переменная 2 | 1 | осталось |
| переменная 2 | 2 | правильно |
| переменная 2 | 3 | вниз |
| переменная 3 | 1 | высокий |
| переменная 3 | 2 | Средний |
| переменная 3 | 3 | низкий |
То, что я хочу, — это те три переменные с ответами людей в качестве ‘ResponseLabel’, например:
| Переменная 1 | переменная 2 | переменная 3 |
|---|---|---|
| Синий | правильно | низкий |
| красный | вниз | Средний |
| Зеленый | осталось | высокий |
| Зеленый | вниз | Средний |
| красный | правильно | низкий |
Обычно я бы шел один за другим и заменял каждую переменную вручную, но я предпочел бы иметь корневой канал, чем делать это для сотен с лишним переменных с где-то от 2-100 ответов, поэтому я полагаю, что есть лучший способ сделать это.
До сих пор я думал о создании таблиц для каждой переменной и слиянии с исходной таблицей, но это тоже звучит слишком много. Затем я подумал, что, может быть, мне следует написать функцию для итерации по исходной таблице и перекодирования каждого столбца один за другим. Я также думаю, что, возможно, есть способ сделать это в M, но я не уверен.
У вас есть какие-нибудь идеи? Спасибо!
Комментарии:
1. Не было бы проще сделать объединение вместо замены?
2. Вы имеете в виду объединение, как при объединении таблиц в PQ, или объединение, как при создании связи между таблицами? спасибо за редактирование: чтобы уточнить, существует более ста столбцов, и в каждом столбце может быть сто вариантов ответа с различиями.
3. Я думаю, вы могли бы просто объединить эти более ста столбцов в один и выполнить соединение только один раз.
4. Это и есть тот самый путь. Открепите обе таблицы, объедините с помощью левого внешнего соединения, разверните, готово.
5. Я попробовал объединить, но не думаю, что я правильно откорректировал свои таблицы. исходная таблица Здесь — метки , здесь — слияние двух , а вот расширенные результаты , которые неупорядочены, и я не уверен, как вернуть это в пригодный для использования формат. Где я ошибся?
Ответ №1:
Предположим, что ваша таблица ключей ответов имеет вид Table1 и загружена в Powerquery
Для верхней таблицы загрузите в PowerQuery
Добавить столбец … индексный столбец …
Щелкните правой кнопкой мыши новый индексный столбец и … Отключите другие столбцы …
Главная.. Объединять запросы …
Установите для нижней таблицы значение Table1 и щелкните, чтобы сопоставить атрибут с VarName и значение с ResponseKey, с левым внешним соединением
Щелкните стрелки над новым столбцом и [x] разверните ResponseLabel
Щелкните правой кнопкой мыши столбец значений и удалите
Нажмите кнопку выбрать столбец атрибута, затем Преобразовать .. сводные столбцы … значение = Метка ответа и Дополнительно= не агрегировать
Щелкните правой кнопкой мыши индексный столбец и удалите
Выполнено
Полный код:
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Attribute", "Value"},Table1,{"VarName", "ResponseKey"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"ResponseLabel"}, {"ResponseLabel"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table1",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "ResponseLabel"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in #"Removed Columns1"