Манипуляции с добавленными или удаленными столбцами в 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 для интернет-маркетинга”

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

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

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

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

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

© 2018    NeedForData