Как динамически объединять столбцы в таблице Power Query

В этом руководстве мы рассмотрим интересный вопрос, поднятый на о том, как динамически объединять изменяющееся количество столбцов в таблице Power Query. Вы можете посмотреть полное видео этого урока в нижней части этого блога.

Этот сценарий имеет дело с экспортом необработанных данных, из-за которого данные распределяются по неизвестному количеству соседних столбцов. Когда вы посмотрите на пример, вы увидите, что описание перенесено в столбец 4, а описание 2 разделено на два соседних столбца. Но так будет не всегда. В следующем цикле это количество столбцов может измениться.

Прежде чем мы перейдем к решению, давайте рассмотрим M-код, который генерирует Power Query при объединении столбцов.

Оглавление

• Таблица Power Query M-код
• Объединение изменяющегося количества столбцов в таблице Power Query
• Заключение

Таблица Power Query M-код

Есть несколько способов объединить столбцы. Один из них — выбрать столбец описания и, нажав либо клавишу Shift, либо клавишу управления, затем также выбрать столбец 4. Затем щелкните правой кнопкой мыши и выберите «Объединить столбцы» .

Или на вкладке «Преобразование» выберите «Объединить столбцы» . И появится это диалоговое окно, которое позволяет вам выбрать разделитель.

Во всплывающем окне выберите Tab, и вы можете ввести новое имя столбца. В данном случае я назвал его Описание.

В строке формул мы видим М-код, созданный на этом шаге преобразования.

Если панель формул не отображается на экране, перейдите на вкладку «Вид» и включите ее.

Таким образом, функция, которую Power Query использует для объединения столбцов, называется Table.CombinedColumns . Первым параметром, который он принимает, является таблица. Эта таблица возвращается предыдущим шагом нашего кода, поэтому в Прикладных шагах вы увидите, что она называется Source .

Затем он жестко закодировал имена столбцов в списке. Здесь вы видите инициализаторы списка, а также текстовые значения, описание имени столбца и столбец имени столбца 4. Затем он вызывает другую функцию M для объединения текстовых значений в этих столбцах.

И, наконец, он передает имя нового столбца в виде текста, чтобы мы могли его настроить.

Поэтому, если мы хотим, чтобы эта функция динамически объединяла изменяющееся количество столбцов, нам придется изменить значение этого второго параметра, которое теперь содержит список жестко закодированных имен столбцов.

Объединение изменяющегося количества столбцов в таблице Power Query

Я вернусь к моему запросу необработанных данных, и если мы внимательно посмотрим на имена столбцов, мы увидим, что каждый из столбцов разлива является анонимным. У них нет собственных имен, но все они начинаются с текстового столбца, за которым следует число. Посмотрим, сможем ли мы это использовать. Сначала я создам ссылку , щелкнув здесь правой кнопкой мыши и выбрав «Ссылка» .

И чтобы получить имена столбцов внутри строки формул, я могу добавить Table.ColumnNames . Эта функция возвращает список со всеми именами столбцов из этой таблицы. Давайте превратим это обратно в таблицу, нажав To Table .

В этом всплывающем окне нажмите OK.

Затем на вкладке «Добавить столбец » я выберу «Формат» , а затем «Обрезать» .

Это не то преобразование, которое я хочу выполнить, но оно генерирует для меня большую часть M-кода. Все, что мне нужно сделать, это заменить эту функцию обрезки моей собственной логикой .

Таким образом, внутри строки формул вместо Text.Trim мы можем сказать, если Text.StartsWith , а затем указать его на наш столбец 1 , и я немедленно скопирую его. Ему нужен тот текст, который мы ищем, так что это будет Column. Поэтому, если он начинается с текстового столбца, мы хотим «null» — для всего остального нам нужно то, что находится в столбце 1. Мы также можем переименовать этот столбец, поэтому внутри строки формул вместо Trim назовем его GroupColumn .

Теперь все, что нам нужно сделать, это заполнить их этими значениями. Поэтому я щелкну правой кнопкой мыши свой заголовок, выберу Fill и Down .

Таким образом, в следующий раз, когда данные поступят и количество столбцов изменится, эта группа автоматически подберет их.

Давайте переименуем этот запрос, я назову его группы столбцов.

Давайте удостоверимся, что его загрузка отключена, потому что это всего лишь вспомогательный запрос.

Я могу сослаться на этот вспомогательный запрос, и я переименую его позже. Оставим это пока. Итак, если вы помните, мы начали с объединения двух столбцов и создали жестко запрограммированный список с именами столбцов , но теперь мы можем фильтровать по описанию в нашей группе столбцов .

Итак, если мы выполним этот фильтр для описания внутри столбца 1, мы получим столбцы, соответствующие этим критериям.

Однако это таблица, а не список. Нам нужно извлечь все, что осталось в этом первом столбце, потому что нам нужно включить эти столбцы в нашу операцию слияния. Чтобы сделать это, мы можем щелкнуть правой кнопкой мыши заголовок столбца 1 и выбрать «Детализация» .

И теперь мы получаем список. Итак, давайте дадим этому запросу правильное имя ( ListDescr ) и проверим, отключен ли он от загрузки.

Теперь мы можем вернуться к нашему запросу Result и заменить этот второй аргумент нашим динамическим списком. Итак, здесь мы можем сослаться на наш ListDescr .

Давайте повторим это и для описания 2. Я перейду к моему запросу ColumnGroups. Я создам еще одну ссылку и выберу нужные столбцы. Затем я отфильтрую описание 2, которое вернет эти три столбца. Я щелкну правой кнопкой мыши свой заголовок и выберу «Детализация», а также переименую этот запрос ( ListDescr2 ).

Затем я вернусь к моему запросу «Результат», выберу столбец «Описание2» и нажму либо Shift, либо Control, чтобы также выбрать следующий столбец (столбец 2). Я щелкну заголовок правой кнопкой мыши и выберу Merge Columns .

Во всплывающем окне я выберу Tab в качестве разделителя и назову это Description2.

Я просто изменю имя столбца здесь, в строке формул. Я также изменю этот жестко закодированный список из списка, который мы только что создали. И теперь, как вы можете видеть, это также подняло столбец 8, который мы ранее исключили.

Как объединить запросы в LuckyTemplates
Параметры запроса LuckyTemplates: оптимизация таблиц
Сортировка столбцов таблицы дат в LuckyTemplates

Заключение

В этом блоге я показал вам, как использовать M-коды в Power Query для динамического объединения столбцов. Это несложно, если вы знакомы с тем, как работает Power Query и какие М-коды использовать.

Я надеюсь, вам понравилось это. Пожалуйста, ознакомьтесь с приведенными ниже ссылками для получения дополнительной информации по теме.

Всего наилучшего!

Мелисса