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