Манипуляции с добавленными или удаленными столбцами в Power Query

Текст представляет собой адаптированный перевод статьи Криса Вебба (Chris Webb), оригинал — Handling Added Or Missing Columns 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-файлами знает, что они имеют неприятное свойство менять свою структуру, даже если это не предполагалось изначально. Неожиданное добавление или удаление столбцов приносит немало проблем при дальнейшей обработке.

Ken Puls в своём блоге показывает, как легко защититься от новых столбцов в источнике данных. При создании запроса выделите все нужные столбцы, нажмите ПКМ и выберите пункт Remove Other Columns (Удалить другие столбцы):

p1.png

Это означает, что если появятся какие-либо новые столбцы в источнике данных, то их не будет в результатах запроса. В коде для этого используют функцию Table.SelectColumns() function.

Работа с удалёнными столбцами несколько сложнее. Прежде всего, нам потребуется список столбцов, которые должны присутствовать в запросе. Его можно вручную создать на листе Excel, или получить через запрос к источнику данных с помощью функции Table.ColumnNames():

let
    //Подключение к файлу to CSV
    Source = Csv.Document(
                      File.Contents(
                       "C:\Users\Chris\Documents\Power Query demos\SampleData.csv"
                      ),null,",",null,1252),
    //Первая строка - заголовки
    FirstRowAsHeader = Table.PromoteHeaders(Source),
    // Получаем список имён столбцов
    GetColumns = Table.ColumnNames(FirstRowAsHeader),
    //Преобразуем список в таблицу
    MakeATable = Table.FromList(
                              GetColumns,
                              Splitter.SplitByNothing(),
                              null,
                              null,
                              ExtraValues.Error),
    //Переименуем единственный столбец таблицы
    RenamedColumns = Table.RenameColumns(
                                         MakeATable ,
                                         {{"Column1", "ColumnName"}})
in
    RenamedColumns  

Наш csv-файл выглядит так:

p2.png

Запрос вернёт такую таблицу:

p3.png

Затем можно сохранить результаты запроса на лист Excel для дальнейшего использования. Только не обновляйте запрос!

Имея список ожидаемых имён, сравним его со списком столбцов источника данных. Например так:

   let  
      //Подключаемся к таблице со списком имён столбцов  
      ExcelSource = Excel.CurrentWorkbook(){[Name="GetColumnNames"]}[Content],  
      //Получаем список  
      ExpectedColumns = Table.Column(ExcelSource, "ColumnName"),  
      //Подключаемся к файлу CSV  
      CSVSource = Csv.Document(  
            File.Contents(  
               "C:\Users\Chris\Documents\Power Query demos\SampleData.csv"  
               ),null,",",null,1252),  
      //Первая строка - заголовки  
      FirstRowAsHeader = Table.PromoteHeaders(CSVSource),  
      //Получаем список заголовков файла csv  
      CSVColumns = Table.ColumnNames(FirstRowAsHeader),  
      //Ищем удалённые столбцы  
      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

Структура тестового файла:

p4.png

Если таблица Excel содержит имена Month, Product и Sales, то запрос вернёт:

p5.png

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

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

Следующая статья
Предыдущая статья

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

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

Набор на новый поток курса по Power BI

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

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

Бесплатная шпаргалка по визуализации
Плакат Как что визуализировать
Сертификаты студентов курсов NeedForData.ru

© 2018    NeedForData