Тонкости работы со столбцами в Power Query
Текст представляет собой адаптированный перевод статей Chris Webb (Крис Вебб), оригинал — Ensuring Columns Are Always Present In A Table Returned By Power Query и Checking Columns Are Present In Power Query. Рассматривается англоязычный Power Query.
Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.

Тонкости работы со столбцами в Power Query

Исчезнувшие или переименованные столбцы могут стать причиной многих проблем при работе с Power Query. Таких, как: ошибки при обновлении запроса, нарушение расчетов в сводных таблицах, самостоятельное переформатирование последних, требующее ручной корректировки. Так что неплохо было бы выстроить логику запроса так, чтобы обеспечивалось наличие в возвращаемой таблице необходимых столбцов.

Рассмотрим следующий csv-файл:
Если мы создадим запрос и подключимся к файлу, то получим приблизительно такой код:
let
    Source = Csv.Document(File.Contents("C:\Demo.csv"), null,",",null,1252),
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"Sales", Int64.Type}})
in
    #"Changed Type"
Предположим наш запрос называется GetSourceData. Предположим также, что выходные данные должны быть таблицей с тремя столбцами: Product, Month и Sales. При этом Product и Month должны иметь текстовый формат, а Sales — числовой. Основные шаги, чтобы обеспечить это, даже в случае изменения структуры csv-файла, следующие:

  1. Создать запрос, подключающийся к источнику данных, подобно запросу GetSourceData из вышеприведённого примера.
  2. Создать запрос, всегда возвращающий таблицу с необходимыми столбцами, но не содержащую строк.
  3. Добавить вторую таблицу к первой. Результатом будет таблица, содержащая все столбцы обеих таблиц.
  4. Удалить ненужные столбцы.
Существует несколько способов создания пустой таблицы на втором шаге. Можно использовать функцию #table(). Следующая строка кода демонстрирует, как это сделать.
#table(  
    type table [Product=text, Month=text, Sales=number],  
    {})
Если вы хотите, чтобы конечный пользователь мог конфигурировать результат под себя, можно воспользоваться таблицей Excel. Например, такой:
Затем транспонировать её, использовать первую строку полученной таблицы как строку заголовков и установить типы данных для каждого столбца.
let
    Source = Excel.CurrentWorkbook(){[Name="Columns"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"First Row as Header" = Table.PromoteHeaders(#"Transposed Table"),
    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",
    {{"Product", type text}, {"Month", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type"
Пусть этот запрос называется ExpectedColumns.

Создать третий запрос, добавляющий ExpectedColumns в конец запроса GetSourceData, тривиальная задача. Если в запросе GetSourceData присутствуют все ожидаемые столбцы, то ничего не изменится. Если нужные столбцы были переименованы или удалены, то мы получим таблицу, в которой будут все столбцы из обоих запросов. Например, переименуем Month в Months, получим такой результат:
Наконец, в третьем запросе выделяем нужные столбцы (т.е. те, которые есть в запросе ExpectedColumns), щелкаем ПКМ и выбираем пункт Remove Other Columns. Этим мы удаляем лишние столбцы. Для предыдущего примера получим:
Несмотря на то, что столбец Month потерял все названия месяцев и содержит только значения null, при дальнейшей обработке ошибки не будут возникать.

Код третьего запроса:
let
    Source = GetSourceData,
    Append = Table.Combine({Source,ExpectedColumns}),
    #"Removed Other Columns" = Table.SelectColumns(Append,{"Product", "Month", "Sales"})
in
    #"Removed Other Columns"
В качестве бонуса — код ещё одного запроса. В нём сравниваются столбцы из GetSourceData и ExpectedColumns, после чего выводится список добавленных/удалённых столбцов.
let
    //Соединимся с таблицей Excel, содержащей ожидаемые заголовки
    ExcelSource = Excel.CurrentWorkbook(){[Name="Columns"]}[Content],
    //Получаем список ожидаемых заголовков
    ExpectedColumns = Table.Column(ExcelSource, "ColumnName"),
    //Список заголовков файла csv
    CSVColumns = Table.ColumnNames(GetSourceData),
    //Ищем отсутствующие столбцы
    MissingColumns = List.Difference(ExpectedColumns, CSVColumns),
    //Ищем добавленные столбцы
    AddedColumns = List.Difference(CSVColumns, ExpectedColumns),
    //Все изменения
    OutputMissing = if List.Count(MissingColumns)=0 then
                     "No columns missing" else
                     "Missing columns: " & Text.Combine(MissingColumns, ","),
    OutputAdded = if List.Count(AddedColumns)=0 then
                     "No columns added" else
                     "Added columns: " & Text.Combine(AddedColumns, ","),
    Output = OutputMissing & "   " & OutputAdded
in
    Output

Проверка наличия столбцов в источнике данных Power Query

Иногда требуется простая проверка наличия всех необходимых столбцов в источнике данных. В этом нам поможет функция Table.HasColumns().

Проверим наш csv-файл на присутствие трёх столбцов Product, Month и Sales. Следующий запрос возвращает ИСТИНА, если в источнике данных есть все столбцы и ЛОЖЬ в противном случае:
let
    Source = Csv.Document(File.Contents("C:\MissingColumnDemo.csv"),[Delimiter=",",Encoding=1252]),
    PromotedHeaders = Table.PromoteHeaders(Source),
    CheckColumns = Table.HasColumns(PromotedHeaders, {"Product", "Month", "Sales"})
in
    CheckColumns