power query разница дат в днях

Power Query. Погружение в М. Дата, время длительность

Создать значение с типом данных #date

Создаем значение #date.

Дата может быть в далеком будущем.

Дата может быть только после Рождества Христова. Дата до РХ вернет ошибку.

Создать значение с типом данных #time

Создать значение #time.

Секунды дробятся с точностью 100 наносекунд, т. е. до 7 знаков после запятой (точки).

Полночь

Следующий код выводит на экран значение 0:00:00.

Следующий код тоже выводит на экран значение 0:00:00.

Если мы сравним эти значения, то в результате получим FALSE, т. е. PQ не считает эти значения равными.

Преобразуем оба этих значения в число. Первая формула вернет 0, а вторая 1.

#datetime

Создадим значение с типом данных datetime.

Если объединить символом & значения #date и #time, то в результате получится значение #datetime. Порядок слагаемых не имеет значения.

#datetimezone

Значение datetimezone — это datetime и еще количество часов и минут относительно UTC. Если последние 2 числа — это 3 и 0, то это значит +3 часа 0 минут ко времени UTC.

#duration (длительность)

Создаем значение с типом данных длительность.

Длительность может быть отрицательной.

Одну и ту же длительность можно получить разными способами.

Параметры #duration могут быть с разным знаком. Результатом будет сложение всех параметров.

Преобразование типов данных

Преобразовать #date в #datetime. Время получится 00:00:00.

Превратим #datetime просто в #date или просто #time.

Превратить #date или #datetime в #datetimezone

При преобразовании #date или #datetime в #datetimezone в результате получим #datetimezone, в котором 2 последних параметра будут браться из настроек системы.

Превратим #datetimezone в #datetime. При преобразовании берется временная зона из настроек системы.

Сложение и вычитание

#duration можно прибавлять к/вычитать из #date, #time, #datetime, #datetimezone, #duration.

#date можно вычитать из #date, #time из #time, #datetime из #datetime, #datetimezone из #datetimezone.

Умножение и деление

Значение #duration можно умножать и делить на число.

Преобразовать в текст с помощью Text.From

Функция Text.From преобразовывает #date, #time, #datetime, #datetimezone, #duration в текст.

Преобразование в текст с указанием формата с помощью x.ToText

Функции x.ToText преобразовывают #date, #time, #datetime, #datetimezone, #duration с возможностью указать формат и язык. Формула Date.ToText(#date(2021, 9, 1), «d MMMM yy», «en-US») превратит дату 01.09.2021 в 1 September 21.

OLE Date

OLE Date — это количество дней, которое прошло от начальной даты. Формула Date.From(0) вернет начальную дату в Power Query. Формула Date.From(20) вернет дату, которая отстоит на 20 дней от начальной даты.

Преобразовать #date, #time, #datetime, #datetimezone, #duration в OLE Date.

Источник

Функции DateAdd, DateDiff и TimeZoneOffset в Power Apps

Добавление значения даты и времени или поиск разницы в значениях даты и времени, а также преобразование между местным временем и временем в формате UTC.

Описание

Функция DateAdd добавляет указанное число единиц измерения к значению даты и времени. Результатом является новое значение даты и времени. Можно также вычесть число единиц измерения из значения даты и времени, указав отрицательное значение.

Функция DateDiff возвращает разницу между двумя значениями даты и времени. Результатом является целое число единиц измерения.

Возможные единицы измерения, используемые в обеих функциях: Milliseconds, Seconds, Minutes, Hours, Days, Months, Quarters или Years. По умолчанию обе функции в качестве единиц используют Days.

Функция TimeZoneOffset возвращает число минут между местным временем пользователя и временем в формате UTC.

Можно совмещать функции DateAdd и TimeZoneOffset, чтобы выполнять преобразование между местным временем пользователя и временем в формате UTC. Добавляя результат TimeZoneOffset, можно преобразовать местное время в формат UTC, а вычитая его (добавляя отрицательное значение), можно преобразовать время в формате UTC в местное время.

Синтаксис

DateAdd( DateTime, Addition [, Units ] )

DateDiff( StartDateTime, EndDateTime [, Units ] )

TimeZoneOffset( [ DateTime ] )

Примеры

Во всех примерах предполагается, что текущие дата и время — 13:02 15 июля 2013 года.

Простой пример для функции DateAdd

ФормулаОписаниеРезультат
Text( DateAdd( Now(), 3 ),
«dd-mm-yyyy hh:mm» )
Добавляет три дня (единицы измерения по умолчанию) к текущему значению даты и времени.«18-07-2013 13:02»
Text( DateAdd( Now(), 4, Hours ),
«dd-mm-yyyy hh:mm» )
Добавляет четыре часа к текущему значению даты и времени.«15-07-2013 17:02»
Text( DateAdd( Today(), 1, Months ),
«dd-mm-yyyy hh:mm» )
Добавляет один месяц к текущему значению даты без указания времени, так как Today не возвращает составляющую времени.«15-08-2013 00:00»
Text( DateAdd( Now(), ‑30, Minutes ),
«dd-mm-yyyy hh:mm» )
Вычитает 30 минут из текущего значения даты и времени.«15-07-2013 12:32»

Простой пример для функции DateDiff

ФормулаОписаниеРезультат
DateDiff( Now(), DateValue(«1/1/2014») )Возвращает разницу между двумя значениями в днях, являющихся единицами измерения по умолчанию.170
DateDiff( Now(), DateValue(«1/1/2014»), Months )Возвращает разницу между двумя значениями в месяцах.6
DateDiff( Now(), Today(), Minutes )Возвращает разницу между текущим значением даты и времени и текущей датой (без указания времени) в минутах. Так как значение Now следует после значения Today, то результат будет отрицательным.-782

Разница дат с дробными результатами

Функция DateDiff возвращает только целое число вычитаемых единиц, а точность задается в указанных единицах. Чтобы вычислить разницу с более высокой точностью, используйте меньшие единицы и соответствующим образом преобразуйте результат, как в примерах ниже.

ФормулаОписаниеРезультат
DateDiff( TimeValue(«09:45:00»), TimeValue(«10:15:36»), Hours )Минуты/секунды игнорируются, разница основана на времени с точностью до часа.1
DateDiff( TimeValue(«09:45:00»), TimeValue(«10:15:36»), Minutes )/60Минуты используются для разницы, а результат делится на 60, чтобы получить разницу в часах.0.5
DateDiff( TimeValue(«09:45:00»), TimeValue(«10:15:36»), Seconds )/3600Минуты и секунды используются для разницы, а результат делится на 3600, чтобы получить разницу в часах.0.51

Преобразование в формат UTC

Чтобы выполнить преобразование в формат UTC, добавьте значение TimeZoneOffset для заданного времени.

Например, представьте, что сейчас 13:02 15 июля 2013 года по летнему тихоокеанскому времени США (UTC-7). Чтобы определить текущее время в формате UTC, используйте следующую команду:

По умолчанию TimeZoneOffset вычисляется для текущего времени, поэтому его не требуется передавать как аргумент.

Чтобы увидеть результат, используйте функцию Text в формате dd-mm-yyyy hh:mm. Она вернет 15-07-2013 20:02.

Преобразование из формата UTC

Чтобы преобразовать время из формата UTC в местное время, следует вычесть значение TimeZoneOffset (то есть добавить отрицательное значение) из заданного времени.

Для примера предположим, что значение даты и времени в формате UTC, 20:02 15 июля 2013 года, хранится в переменной StartTime. Чтобы настроить время в соответствии с текущим часовым поясом пользователя, используйте следующую команду:

Обратите внимание, на знак минус перед TimeZoneOffset, который позволяет вычесть смещение, а не добавить его.

Чтобы увидеть результат, используйте функцию Text в формате dd-mm-yyyy hh:mm. Она вернет 15-07-2013 13:02, если вы находитесь в часовом поясе тихоокеанского времени США (лето).

Источник

Функции для работы с датами и временем

В этой статье

Эти функции позволяют создавать значения параметров и zone и управлять ими.

Дата и время

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

Сообщений: 24 Регистрация: 27.05.2020

У моей модели в PBI есть одна колонка с датами, даты расположены хаотично, но это не принципиально.

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

Как это сделать? Желательно в двух вариантах:

1. в котором не нужно будет создавать дубликат столбца с датами, т.е. минимальная и максимальная дата определяется только из одного столбца

2. с созданием дубликата столбца дат и поиска в одном столбце минимальную дату, в дубликате искать максимальную дату и в итоге на разнице дат, определять количество месяцев.

Сообщений: 6143 Регистрация: 22.02.2017

Цитата
Георгий Шкуро написал:

даты расположены хаотично

Умею писать хаотичный код, но не уверен что вам это поможет.

Вы бы в файлике хоть примерно структуру источника отобразили бы. А так я даже не знаю чем помочь.

Изменено: PooHkrd — 27.05.2020 17:35:21

Вот горшок пустой, он предмет простой…

Сообщений: 254 Регистрация: 03.12.2018

Георгий Шкуро, грубо:

Код
add = Table.AddColumn(YourTable, «diff», each let a = YourTable[sColumn] in Number.RoundDown(Number.From((List.Max(a) — List.Min(a))/30)))
Георгий Шкуро

Сообщений: 24 Регистрация: 27.05.2020

Цитата
PooHkrd написал:

Вы бы в файлике хоть примерно структуру источника отобразили бы.

Изменено: Георгий Шкуро — 27.05.2020 23:30:44

Сообщений: 24 Регистрация: 27.05.2020

Попробовал, что-то пошло не так.

Сообщений: 11749 Регистрация: 22.12.2012

Доброе время суток.

Цитата
Георгий Шкуро написал:

что-то пошло не так.

Цитата
Aleksei_Zhigulin написал:

YourTable

PooHkrd

Сообщений: 6143 Регистрация: 22.02.2017

Цитата
Георгий Шкуро написал:

Формулу подрихтовал для быстродействия. Так пробуйте:

Код
= Table.AddColumn( #»Измененный тип2″, «Пользовательская», let a = List.Buffer(#»Измененный тип2″[Дата транзакции]), max = List.Max(a), min = List.Min(a) in each Number.RoundDown( Number.From(max — min)/30 ) )

Вот горшок пустой, он предмет простой…

Сообщений: 254 Регистрация: 03.12.2018

PooHkrd, доброго дня,

Довольно редко сталкиваюсь с необходимостью оптимизировать быстродействие запросов, но разве List.Buffer здесь что-то ускоряет? Мне казалось, что при конструкции let a = list in List.Max(a) — List.Min(a) движок будет обращаться к списку a только один раз, неважно, сколько раз этот список будет встречаться в выражении in.

Сообщений: 6143 Регистрация: 22.02.2017

Aleksei_Zhigulin, это да, но если вы обращаетесь к списку после each то обращений будет столько же сколько строк в таблице * 2, т.к. вы считаете и мин и макс. если же сначала посчитать все данные, которые не зависят от контекста строки (во загнул ), и в each передавать уже посчитанные скалярные переменные, то обращений будет всего 2. Буфер в моем случае чуть-чуть ускорит процесс для очень большой таблицы, тогда к нему будет обращение 1 раз вместо двух.

Тут ведь как, мы сейчас обсуждаем сферического коня в вакууме, а по факту эти вычисления могут быть уже 20-тым по счету шагом, и обращение к столбцу из-за ленивки могут рассчитываться очень долго.

Вот горшок пустой, он предмет простой…

Сообщений: 254 Регистрация: 03.12.2018

PooHkrd, точно, спасибо! Порядок each упустил из виду. Вот эту встроенную конструкцию неправильно вспомнил:

Код
= Table.SelectRows(Source, let earliest = List.Min(Source[Col1]) in each [Col1] = earliest)

Изменено: Aleksei_Zhigulin — 28.05.2020 14:42:01

Сообщений: 6143 Регистрация: 22.02.2017

Aleksei_Zhigulin, в вашей формуле из поста №10, нет нельзя.

В чем финт первой конструкции, которая формируется кодом по кнопке фильтрации самой ранней даты? В том, что результатом, который присваивается переменной earliest является скалярное значение, которое согласно спецификации языка рассчитывается один раз и сразу кладется в память. В вашей второй конструкции вы присваиваете переменной а элемент составного типа, который рассчитывается лениво, т.е. каждый раз при обращении к нему. Что будет происходить? При каждой итерации будет рассчитывать мин/макс списка а, при этом транслятор когда будет видеть переменную а, будет действовать так: это список, а как его посчитать? Ага вон код, и будет прогонять вообще весь расчет всех предыдущих шагов, для получения одного и того же списка. Т.е. функция вашей конструкции только в том, чтобы укоротить код. Чтобы увеличить производительность нужно как минимум положить список в память, а еще лучше до each посчитать оба скалярных значения, сразу же их разность и в each передавать одно готовое скалярное значение. Вот это самое оптимальное.

Вот горшок пустой, он предмет простой…

Сообщений: 24 Регистрация: 27.05.2020

Цитата
PooHkrd написал:

Уже теплее, результат определен правильно, в модели 19 месяцев, только мне нужно чтобы это значение было расположено именно в столбце.

Сообщений: 6143 Регистрация: 22.02.2017

Цитата
Георгий Шкуро написал:

А вы все никак не научитесь копировать решения в свой код. Где в моей формуле вы увидели вот это?

Я про этот самый each распинаюсь тут уже час в обсуждении с Алексеем, а вы это все игнорируете.

Изменено: PooHkrd — 28.05.2020 15:31:56

Вот горшок пустой, он предмет простой…

Сообщений: 254 Регистрация: 03.12.2018

PooHkrd, моя вторая конструкция с вопросом провисела всего пару минут, но Вы таки успели на него ответить

20 сек.). Правда, проверял на числах, а не датах.

Код
= Table.AddColumn(types, «aaa», let a = types[Столбец2] in each List.Max(a) — List.Min(a))
Код
= Table.AddColumn(types, «aaa», let a = List.Buffer(types[Столбец2]), b = List.Max(a), c = List.Min(a) in each b — c)

Это наводит на крамольные мысли, что лист a запоминается один раз, а не пересчитывается в каждой строке.

Сообщений: 24 Регистрация: 27.05.2020

Цитата
PooHkrd написал:

А вы все никак не научитесь копировать решения в свой код.

Виноват, не заметил. Теперь результат идеальный!

Изменено: Георгий Шкуро — 28.05.2020 16:22:40

Сообщений: 24 Регистрация: 27.05.2020

PooHkrd, есть еще вопрос: как в Power Query посчитать количество строк по каждому участнику, к примеру сколько строк по номеру 9465776802285679 и т.д. по каждому номеру.

Сообщений: 6143 Регистрация: 22.02.2017

Цитата
Георгий Шкуро написал:

Это в ветке работа за деньги так можно в одной теме накидывать. А в бесплатной ветке Один вопрос — одна тема.

Цитата
Aleksei_Zhigulin написал:

Это наводит на крамольные мысли

Я давно замечал, что итератор в AddColumns как-то иначе работает чем в остальных функциях. Но чтобы не запоминать где и чем оно отличается всегда такие расчеты делаю до each а потом результат уже передаю в функцию. Еще ни разу не подводило.

Изменено: PooHkrd — 28.05.2020 16:59:28

Вот горшок пустой, он предмет простой…

Сообщений: 254 Регистрация: 03.12.2018

Цитата
PooHkrd написал:

итератор в AddColumns как-то иначе работает чем в остальных функциях

Да, по-моему, на форуме уже поднималась эта тема, потому и запомнилось. Спасибо!

Сообщений: 24 Регистрация: 27.05.2020

Цитата
PooHkrd написал:

А в бесплатной ветке Один вопрос — одна тема.

Услышал. Благодарю за решение. Спасибо!

Функции данных

В этой статье

Источник

Глава 24. Динамический календарь в Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

С помощью Power Query довольно легко настроить полностью динамический календарь в Excel. Благодаря идеям главы 23 вы сможете управлять им на основе значений в двух ячейках Excel. Вам предстоит пройти четыре шага:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.1. Таблица параметров

Скачать заметку в формате Word или pdf, примеры в формате архива

Добавление таблицы параметров

Функция fnGetParameter

Откройте файл fnGetParameter.txt. Скопируйте его содержимое в буфер. Вернитесь в книгу Excel, пройдите по меню Данные –> Получить данные –> Из других источников –> Пустой запрос. В редакторе Power Query перейдите на вкладку Главная –> Расширенный редактор. Выделите весь код в окне и нажмите Ctrl+V –> Готово. Переименуйте запрос fnGetParameter.

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.2. Функция fnGetParameter готова к работе

Построение структуры календаря

Продолжим. Создайте новый запрос не выходя из редактора Power Query. Главная –> Создать источник –> Другие источники –> Пустой запрос. Переименуйте запрос Calendar. Самый простой способ начать строить календарь – создать простой список. Введите в строке формул: =<1..10>. Нажмите Enter. Вы создали список от одного до десяти:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.3. Теперь у вас есть простой список

Преобразуйте этот список в таблицу и посмотрите, что произойдет, если изменить тип данных –> Дата. Перейдите на вкладку Средства для списков –> Преобразование –> В таблицу. Оставьте параметры по умолчанию и нажмите Ok. Щелкните правой кнопкой мыши столбец Column1 –> Тип изменения –> Дата. Щелкните правой кнопкой мыши столбец Column1 –> Переименовать –> Date. Хотя это не тот диапазон дат, который вам нужен, вы получили фрагмент календаря:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.4. Календарь; правда, немного устаревший))

Теперь нужно вставить пару шагов в начале запроса, чтобы извлечь даты начала и окончания, а затем передать эти даты в список вместо «от 1 до 10». Перейдите на вкладку Главная –> Расширенный редактор. После строки let добавьте две строки кода M:

startdate = fnGetParameter( » Start Date » ),

enddate = fnGetParameter( » End Date » ),

Не забудьте про запятые в конце каждой строки. Нажмите Готово. Убедитесь, что оба шага возвращают «правильные» даты:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.5. Start Date возвращает 01.01.2014

Замените числа 1 и 10 переменными. Выберите шаг Источник. Измените формулу:

Нажать Enter. К сожалению, возвращается ошибка:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.6. Что не так?

startdate = Number.From(fnGetParameter( » Start Date » )),

enddate = Number.From(fnGetParameter( » End Date » )),

Нажмите Готово. Перейдите на шаг startdate. Дата отражается в виде целого числа 41640. Перейдите к последнему шагу запроса, вы увидите таблицу, начинающуюся с 1 января 2014 года:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.7. Календарь готов к использованию

Добавление столбцов календаря

Выберите столбец Date –> Добавление столбца –> Дата –> Год –> Год. Выберите столбец Date –> Добавление столбца –> Дата –> Квартал –> Квартал года. Выберите столбец Date –> Добавление столбца –> Дата –> Месяц –> Месяц. Выберите столбец Date –> Добавление столбца –> Дата –> День –> День недели.

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.8. В календарь добавлены часто используемые столбцы

Функции даты в Excel и Power Query

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.9. Примеры функций Excel

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.10. Сравнение функций в Excel и Power Query

Сложение дат

Вы будете разочарованы, если попытаетесь взять дату и добавить к ней число. Даже несмотря на то, что даты – это числа, Power Query не может неявно преобразовать тип данных, как это умеет делать Excel. Поэтому в Power Query существует коллекция функций для добавления дат:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.11. Функции сложения дат в Excel и Power Query

Даты как текст

Для возврата дат в текстовом формате в Excel используют функцию ТЕКСТ(). В Power Query аналогичным целям служит функция Date.ToText(). С последней нужно держать ухо востро: не только имя функции чувствительно к регистру, но и ее параметры. Поскольку Power Query не русифицирован, то и текст он возвращает английский:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 24.12. Даты как текст; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Источник

Глава 17. Формулы Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Пользовательский интерфейс Power Query позволяет выполнять огромное число операций. Но наверняка возникнут моменты, когда вам потребуется что-то сделать, что не встроено в интерфейс. Вот мы и добрались до языка программирования Power Query: M.

Хотя некоторые аспекты M могут быть довольно сложными, есть простой способ получить представление о языке, начав с формул в пользовательских столбцах. Поскольку Power Query был создан для профессионалов Excel, можно было ожидать, что его язык будет подобен языку формул Excel. К сожалению, это не так.

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.1. Интерфейс создания пользовательского столбца

Скачать заметку в формате Word или pdf, примеры в формате архива

Создание пользовательских столбцов

Окно создания настраиваемого столбца содержит три важные части (см. 17.1):

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

Power Query построит формулу: =[Class]&[# » Account #2 » ]

К сожалению, Power Query не имеет подсказок, чтобы выяснить, какие функции можно использовать. Но… в окне Настраиваемый столбец есть гиперссылка Сведения о формулах Power Query (см. рис. 17.1). Щелкнув на нее, вы попадете на страницу с подробным каталогом функций, правда, на английском языке. В отличие от Excel, но аналогично Power Pivot, функции Power Query не русифицированы.

Подводные камни формул на языке М

Power Query и Excel существенно различаются в том, как они обрабатывают входные данные.

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.2. Различия Power Query и Excel в обработке данных

Чувствительность к регистру. Запомните, что в 99% случаев первая буква каждого слова в формуле на языке М – заглавная, а остальные – строчные. В то время как Excel не заботится, какие буквы вы используете и преобразует формулы в верхний регистр по умолчанию, Power Query просто возвращает ошибку.

База 0 против базы 1. Если бы вас спросили о номере позиции буквы x в слове Excel, вы сказали бы 2. Это логично, и так считает программа MS Excel. Но Power Query скажет, что буква x в слове Excel занимает позицию 1.

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

В Excel вы можете объединить две ячейки вместе с помощью функции конкатенации &. Содержат ли ячейки текст или числа, не важно. Excel неявно преобразует их в текст, а затем объединяет вместе:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.3. Неявное преобразование данных в Excel: число и текст, преобразованные в текст

Создайте на основе двух первых столбцов Таблицы Excel запрос, а затем внутри Power Query создаете пользовательский столбец, используя формулу: =[Column1]&[Column2]:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.4. Power Query не может соединить число и текст вместе

Чтобы устранить эту проблему, необходимо сначала преобразовать тип данных Столбец1 в текст, а уже затем создать пользовательский столбец:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.5. Два текстовых столбца объединить можно

При явном преобразовании данных в столбце 1 в текстовое значение конкатенация будет работать так, как вы изначально предполагали:

На самом деле существует два способа работы с типами данных в Power Query:

Функции преобразования типов данных

Существует несколько функций преобразования типов данных.

Преобразование в текст. Если вам нужно преобразовать значения в столбце в текст, можно использовать универсальную функцию Text.From(). Если же вы хотите подчеркнуть тип преобразуемых данных, также в вашем распоряжении есть: Date.ToText(), Time.ToText(), Number.ToText(). Имейте в виду, что Text.From() преобразует любой тип данных в текст, в то время как Date.ToText() не преобразует число в текст.

Даты. Данные, похожие на даты, могут поступать в формате чисел или текста. Для их преобразования есть две функции: Date.From() и Date.FromText(). Опять же, Date.From() справится с преобразованием в формат даты, как чисел, так и текста.

Время. Значения времени могут поступать как в виде чисел, так и в виде текста. Опять же, есть две функции для них: Time.From() и Time.FromText().

Длительность – это разница между двумя значениями даты/времени: Duration.From() и Duration.FromText().

Числа. Имеется универсальная функция Number.From() и несколько специальных. Для чисел из текста Number.FromText(), для десятичных чисел Decimal.From(), целых чисел Int64.From(), валюты Currency.From().

Сравнение текстовых функций Excel и Power Query

Если вы работали с текстовыми функциями Excel, то привыкли использовать их для извлечения элементов текста из данных. В Power Query текстовые функции работают иначе. Рассмотрим пять наиболее часто используемых текстовых функций Excel, и их аналоги в Power Query. Откройте файл 5 Useful Text Functions.xlsx. Каждый из примеров в этом разделе начинается с набора данных:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.6. Пример данных

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

Итак, что поместить данные, представленные на рис. 17.6, в Power Query, кликните на любой ячейке в диапазоне А1:В8 –> Данные –> Из таблицы/диапазоне. Подтвердите создание Таблицы с заголовком. В окне редактора Power Query переименуйте запрос pqLeft. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqLeft(x,4). Введите формулу: =LEFT([Слово],4). Вроде бы, это должно сработать:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.7. Power Query не находит синтаксических ошибок

Однако, после нажатия Ok, появляется ошибка:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.8. Формула =LEFT([Слово],4) не работает

В Power Query используется иной синтаксис =Text.Start(text,num_chars). Отредактируйте формулу. В области ПРИМЕНЕННЫЕ ШАГИ кликните на шестеренку справа от строки Добавлен пользовательский столбец, и в окне Настраиваемый столбец введите формулу: =Text.Start([Слово],4). Не забывайте, что формулы в Power Query чувствительны к регистру: Text.start и TEXT.START вернут ошибку. Нажмите Ok:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.9. Функции ЛЕВСИМВ() в Excel соответствует Text.Start() в Power Query

Теперь вы можете завершить запрос: Главная –> стрелочка вниз возле кнопки Закрыть и загрузить –> Закрыть и загрузить в… –> Только создать подключение.

Никогда не используйте имя функции Excel в качестве имени запроса Power Query. Если бы вы назвали запрос ЛЕВСИМВ, вы бы получили ошибки в формулах Excel исходной Таблицы. Имена таблиц обрабатываются перед функциями.

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.10. Не давайте запросам имена, совпадающие с именами функций Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.11. Соответствие текстовых функций Excel и Power Query

Первые две функции из таблицы аналогичны только что рассмотренной Text.Start(). Использование двух последних функций требует небольшой коррекции в связи с тем, что Power Query за точку начала отсчета берет ноль. Также обратите внимание на следующее различие: аргумент искомый текст является первым в функции НАЙТИ(), и – вторым в функции Text.PositionOf().

Аналог функции НАЙТИ

В файле 5 Useful Text Functions.xlsx перейдите на лист FIND. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqFind. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqFind(x, » o » ). Введите формулу: =Text.PositionOf([Word], » o » ). Нажмите Ok.

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.12. Результат не вполне согласуются с Excel

Возвращаемые значения, следуют базовому правилу. В первой строке буква F идет под номером 0. Измените формулу, добавив 1: =Text.PositionOf([Word], » o » )+1.

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.13. Есть совпадение с Excel, а вместо ошибок выводится значение ноль

Аналог функции ПСТР

В файле 5 Useful Text Functions.xlsx перейдите на лист MID. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqMid. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqMid(x,5,4). Введите формулу: =Text.Range([Word],5,4). Нажмите Ok. Результат не соответствует ожиданиям:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.14. Несколько результатов адекватно, но почему не все?

Это немного удивляет. Вы ожидали, что результат не будет соответствовать Excel. Но что число ошибок будет таким большим!? Для начала исправим положение начального символа. На примере Bookkeeper вы ожидали увидеть keep, а появился eepe. Поскольку первый символ в слове имеет номер ноль, вам нужно исправить формулу на =Text.Range([Word],5-1,4).

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.15. Уже лучше, но всё еще ошибки в двух последних строках

Одна из замечательных особенностей функции Mid (ПСТР) Excel заключается в том, что вас не волнует, сколько символов осталось в текстовой строке. Если конечный параметр больше, чем количество оставшихся символов, он просто вернет все оставшиеся символы. Не таков Power Query. Вам нужно дополнить формулу проверкой: вы хотите вернуть четыре символа или меньше, до конца текстовой строки. Для этих целей подойдет функция List.Min (подробнее о ней вы узнаете из главы 20). Вместо того, чтобы пытаться встроить эту функцию в формулу столбца pqMid(x,5,4), создайте еще один пользовательский столбец с формулой =List.Min().

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.16. В отдельном столбце определено количество оставшихся символов

Несколько слов о том, как работает формула:

Теперь вы можете отредактировать формулу в столбце pqMid(x,5,4) =Text.Range([Word],5-1, List.Min())

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.17. Всё верно, кроме последней строки

Теперь вы можете удалить вспомогательный столбце Пользовательская и загрузить запрос в Таблицу на лист Excel. А как же ошибка в последней строке. Не страшно. Потому что ошибки в Power Query будут показываться в Excel, как пустые ячейки:

power query разница дат в днях. Смотреть фото power query разница дат в днях. Смотреть картинку power query разница дат в днях. Картинка про power query разница дат в днях. Фото power query разница дат в днях

Рис. 17.18. Ошибки исчезают при загрузке в Таблицу

Очень жаль, что не все функции в Power Query эквивалентны функциям в Excel. Особенно учитывая, что Power Query – это инструмент для пользователей Excel. Надеемся, что это изменится в будущих версиях. Мы хотели бы видеть новую библиотеку функций Power Query, которые позволяют переносить существующие знания из Excel в Power Query без необходимости изучать новые функции и новый синтаксис.

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *