Excel прочее

Работа с международной датойи числовыми форматами в Power Query

/

Оригинал в статье → https://needfordata.ru/blog/rabota-s-mezhdunarodnoj-datoj-i-chislovymi-formatami-v-power-query

Адаптированный перевод текста Working With International Date And Number Formats In Power Query,
автор Chris Webb (Крис Вебб). В статье речь идет о надстройке 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 и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.

Одна из проблем, которая действительно может довести до безумия при работе с данными текстовых файлов – это множество форматов дат и чисел, применяемых в них.

Например, взглянем на скриншот, где отражено содержимое файла с разделителем – знаком табуляции:

Здесь одна строка и две колонки: Date and Sales. Что же они означают?

Житель Великобритании прочтет дату как 2-е марта 2015 (в Англии, как и в большей части мира используют формат DD/MM/YYYY – день/месяц/год), а число прочтет как сто тысяч два (в Англии используют точку для отделения дробной части, и запятую для отделения тысяч). Однако для американца дата выглядит как 3-е февраля 2015. А француз прочитает число как сто целых две тысячных. Так что, если вы работаете в одной стране, а получаете данные из другой, то необходимо учитывать эти нюансы.

К счастью, Power Query легко это сделает, даже если механизм не вполне понятен. Вам не придется менять язык Windows, или разбивать даты на составные части и собирать их в правильном порядке, или делать нечто подобное.

При первой загрузке csv файла, подобного показанному выше, Power Query попытается определить тип данных для каждого столбца. Ниже приведён код, сгенерированный Power Query для этого файла:

let   

Источник = Csv.Document(File.Contents(  
"D:\Temp\Перевод\Working With International Date And Number Formats In Power Query\TESTFILE.csv"),  
[Delimiter=" ", Columns=2, Encoding=1251, QuoteStyle=QuoteStyle.None]),
"Повышенные заголовки" = Table.PromoteHeaders(Источник),
"Измененный тип" = Table.TransformColumnTypes(  
#"Повышенные заголовки",{{"Date", type date}, {"Sales", type number}})  

in  

# "Измененный тип"

Как мы видим, автоматически создано три шага:

  1. Источник – загружает данные из CSV файла и определяет разделитель табуляция.
  2. # «Повышенные заголовки» – использовать первую строку данных для заголовков столбцов.
  3. # «Измененный тип» – устанавливает для столбца Date тип данных дата, а для столбца Sales числовой.

В русском варианте Первая строка как заголовки стала Повышенные заголовки (прим. пер.)

Для изменения типа данных столбца, нужно сначала удалить шаг Изменённый тип, щелкнув по крестику слева от пункта меню. После этого щелкаем ПКМ на заголовке столбца Date, выбираем Тип изменения, потом Используя локаль…

Появится диалоговое окно Изменение типа локали:

Локаль – это просто название для всех правил форматов дат и чисел и многого другого, связанного с определённым языком и регионом. Так, например, выбрав Языковой стандарт русский для столбца Date, увидим 03.02.2015.

CSV файл содержит дату «2/3/2015». Мы сказали Power Query, что дата в источнике в формате, используемом в России, после чего дата была выведена в соответствии с настройками для России. Данный пример не очень показателен, если дата будет «2/15/2015», то без ошибок её можно будет вывести путем установки локали на США.

Используя эту технику, установим для столбца Sales тип данных Десятичное число и Языковые стандарты Великобритании:

В результате запятая будет воспринята как разделитель тысяч:

Код этого запроса:

let  

Источник = Csv.Document(File.Contents(  
"D:\Temp\Перевод\Working With International Date And Number Formats In Power Query\TESTFILE.csv"),  
[Delimiter=" ", Columns=2, Encoding=1251, QuoteStyle=QuoteStyle.None]),
# "Повышенные заголовки" = Table.PromoteHeaders(Источник),
# "Измененный тип" = Table.TransformColumnTypes(  
      #"Повышенные заголовки",{{"Date", type date}, {"Sales", type number}}),
# "Измененный тип с языком" = Table.TransformColumnTypes(  
      #"Измененный тип", {{"Date", type date}}, "ru-RU"),
# "Измененный тип с языком1" = Table.TransformColumnTypes(  
      #"Измененный тип с языком", {{"Sales", type number}}, "en-GB")

in

# "Измененный тип с языком1"

Вернемся к русским настройкам для обоих столбцов:

Теперь запятая интерпретируется как разделитель целой и дробной части.

от  Комментарии к записи Работа с международной датойи числовыми форматами в Power Query отключены