Учебное пособие по Microsoft Power Query о том, как исправить смешанные проблемы с фиксированной шириной столбца

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

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

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

Я пытался решить это самостоятельно, но, поскольку это как-то связано с запросом мощности, мне понадобилась помощь Мелиссы. Я думал, что это займет у нее как минимум два дня, но она сразу же нашла решение.

Мелисса покажет нам, как она решила эту сложную задачу. Я думаю, что большинство людей будут иметь дело с такого рода проблемами и ищут пути их решения.

Мелисса: Первый совет: если вы просматриваете файл фиксированной длины, вы можете перейти на вкладку «Вид» и включить параметр «Моноширинный» .

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

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

Я создал параметр для местоположения файла, в котором я сохранил файл CSV. Я принес это как промежуточный файл и добавил его в свой параметр местоположения файла. Затем я создал ссылку, и я буду работать с этой ссылкой. Это то, что мы сейчас рассматриваем в Microsoft power query.

Оглавление

• Добавление столбца индекса
• Создание списка буферов в Microsoft Power Query
• Разделение заголовков из строк
• Удаление нулей и пробелов в Microsoft Power Query
• Разделение разделов
• Обрезка текстовых строк в Microsoft Power Query
• Заключение

Добавление столбца индекса

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

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

Нам понадобится ключ, чтобы собрать эти вещи вместе. Но если ключа нет, то я добавляю столбец индекса . Я нажму на значок мини-таблицы, выберу Add Index Column , затем добавлю From 0 .

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

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

Для начала скопируем это значение:

Добавьте новый пустой запрос, вставьте его и назовите его HeaderID .

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

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

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

Включим нагрузку для этих двух запросов.

Создание списка буферов в Microsoft Power Query

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

Я открою расширенный редактор и размещу его на самом верху. Когда вы используете пользовательский интерфейс для создания кода, он будет ссылаться на предыдущий шаг.

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

Я назову этот BufferList и сошлюсь на Column1. Чтобы загрузить его в память, я добавлю шаг List.Buffer .

Вот моя переменная вверху. Я могу ссылаться на него снова и снова.

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

Я напишу, что если столбец 1 равен нашему идентификатору заголовка, то я хочу, чтобы мой номер индекса был нулевым.

В результате он нашел текст и вернул 5 и 23.

Мне нужно это значение во всех строках, поэтому мне нужно заполнить его. Вы можете просто щелкнуть правой кнопкой мыши, чтобы заполнить, но вы также можете использовать очень простой синтаксис и добавить его в строку формул.

В данном случае я добавил Table.FillDown и в текстовой строке указал, какой столбец мы хотим заполнить (Заголовок).

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

Разделение заголовков из строк

Следующий шаг — отделить заголовки от строк. Я добавлю еще один настраиваемый столбец и назову его Temp . На этот раз мы собираемся сделать что-то более сложное и использовать этот BufferList , который я создал ранее.

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

Я начну с оператора if и использую List.Contains для поиска определенной позиции в BufferList и ссылки на заголовок запроса .

Мы хотим найти его по всей длине файла, а затем вернуть позицию элемента в списке. Если он соответствует индексу, у нас есть совпадение для этой конкретной строки.

Затем я хочу вернуть значение для идентификации заголовка. В этом случае я просто возвращаю H. Я скопирую синтаксис, чтобы мне не пришлось писать все заново.

Нам также необходимо определить раздел строки. Если список содержит не HeaderID , а DetailID , то мы находимся на секции строки.

Если Column1 — это пустая текстовая строка, я хочу, чтобы она оставалась пустой . Если это не так, то я хочу, чтобы это было null .

Эта оппозиция получила строку заголовка и вернула H, а затем нашла подробную строку и вернула R. Затем она вернула 0 для всех элементов, которые являются общими в этом разделе строки.

Эти пробелы или нули важны, потому что они позволяют вам заполнить. Заполнение вниз не будет перемещаться по этим пустым ячейкам, поэтому мы можем удалить их позже.

Мы сделаем это в строке формул и снова воспользуемся Table.FillDown . Ему нужен список с именем столбца, который является нашим столбцом Temp .

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

Вы также можете заполнить его из пользовательского интерфейса, если не хотите писать код. Вы можете просто щелкнуть правой кнопкой мыши и выбрать «Заполнить» , затем «Вниз» .

Удаление нулей и пробелов в Microsoft Power Query

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

Разделение разделов

Как только мы удалим эти пробелы и нули, у нас останется все, что нам нужно. На этом этапе мы можем просто разделить разделы. Мы можем сосредоточиться на строках заголовков и выделить их, потому что они имеют отдельный интервал от всех строк сведений (у которых также есть отдельный интервал).

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

Теперь у меня есть все эти строки заголовков.

Я могу выбрать Column1 , перейти к строке формул, выбрать Split column , а затем разделить по позициям.

Затем попросите сам power query разобраться с этим. Он предложит пару позиций. Нажмите OK , чтобы принять эти позиции.

Из заголовков нас интересуют только артикул и номер задания .

Здесь, в строке формул, я могу переименовать их с помощью Item и Job # . Это избавит меня от другого шага переименования столбца.

После этого шага все, что мне нужно сделать, это выбрать Item , выбрать Job # и, конечно же, выбрать ключ заголовка . Затем я удалю все остальные столбцы, потому что они мне больше не нужны.

Это будет результатом. Нам все еще нужно очистить значения и удалить текстовый элемент и тире. Все, что нам нужно, это те значения, которые находятся между ними.

Итак, мы открываем это и снимаем выделение с тире и элементов.

Теперь все заголовки готовы.

Мы должны проделать тот же процесс и для DetailID . Мне нужно будет переименовать эти шаги, чтобы мне было легче вернуться к ним чуть позже.

Мы вернемся к исходному запросу, который мы начали. Мы начали с «Отфильтрованных строк» ​​на панели «Примененные шаги».

Я скопирую это и добавлю в свой фильтр. На этот раз я не выбираю H, а выбираю R.

Затем я выберу столбец 1, перейду к столбцу «Разделить», разделю по позициям, а затем попрошу силовой запрос выяснить это.

Это то, что предлагает запрос мощности. Давайте попробуем.

Это на самом деле выглядит довольно хорошо. Даже общие ряды идеально разделены. Конечно, там много пробелов, потому что у нас был этот отступ.

Обрезка текстовых строк в Microsoft Power Query

Я выберу этот первый столбец, затем нажму «Вниз» + «Shift», чтобы выбрать столбец 1.10. Перейдите в Transform , выберите Format , затем Trim . Обрезка удалит лишние пробелы только перед или в конце строки, но не между ними.

Затем мы можем просто продвигать заголовки, поэтому мне не нужно вводить все заголовки или заголовки для этих столбцов. На шаге разделения я переименовал два столбца. Теперь, конечно, с 10 столбцами это немного беспокоит.

Мы также должны избавиться от этих избыточных значений. Поскольку у нас есть итоги, я должен использовать один из этих трех последних столбцов, потому что это единственные строки, в которых дополнительные значения находятся где-то посередине. Затем мы отменим выбор этих пробелов, тире и текстов.

Затем я уберу ненужные столбцы, и останется только таблица с заголовками и деталями. Нам нужен ключ, чтобы снова собрать эти секции вместе.

Для этого мы можем использовать самослияние, чтобы мы могли объединить таблицу с самой собой, чтобы снова собрать эту информацию. На вкладке Главная выберите Объединить , затем выберите Столбец 5 и тот же запрос.

Вместо AllDetails я хочу AllHeaders в качестве исходной таблицы, с которой я хочу объединиться.

Это вернуло всю информацию из таблицы заголовков с одной строкой для каждого элемента и каждого номера задания.

Мы использовали ключ для слияния со строками деталей. Если я нажму в сторону в этом пустом месте, мы увидим предварительный просмотр всех строк, принадлежащих заголовку 5.

Здесь мы удалим последний столбец, а затем закончим исправление смешанной фиксированной ширины столбца в запросе мощности Microsoft.

Заключение

В этом руководстве мы придумали способ решения смешанных проблем с фиксированной шириной столбца с помощью Microsoft Power Query. Если вам понравился контент, описанный в этом руководстве, не забудьте подписаться на телеканал LuckyTemplates.

У нас постоянно появляется огромное количество контента от меня и ряда создателей контента, каждый из которых занимается улучшением того, как вы используете LuckyTemplates и Power Platform.

Мелисса