Тонкости работы со столбцами в 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-файл:

p1.png

Если мы создадим запрос и подключимся к файлу, то получим приблизительно такой код:

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],  
    {})  

p2.png

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

p3.png

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

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, получим такой результат:

p4.png

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

p5.png

Несмотря на то, что столбец 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
Следующая статья
Предыдущая статья

Комментирование закрыто.

Идет набор на курс “Power BI для интернет-маркетинга”

Подписка на рассылку NeedForData

Подпишитесь на нашу рассылку, чтобы не пропустить новые статьи!

Подписка на рассылку NeedForData

Бесплатная шпаргалка по визуализации
Плакат Как что визуализировать

Плакат Как что визуализировать

© 2018    NeedForData