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

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

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

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

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

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

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

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

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

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

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

В этой статье

Источник

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

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

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

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

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

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

Функция fnGetParameter

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

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

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

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

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

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

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

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

startdate = fnGetParameter( » Start Date » ),

enddate = fnGetParameter( » End Date » ),

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сложение дат

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

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

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

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

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

Источник

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

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

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

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

Рис. 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 существенно различаются в том, как они обрабатывают входные данные.

Рис. 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 неявно преобразует их в текст, а затем объединяет вместе:

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

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

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

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

Рис. 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. Каждый из примеров в этом разделе начинается с набора данных:

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

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

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

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

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

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

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

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

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

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

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

Рис. 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

• ← power query прибавить к дате дни
• power query разница между датами в днях →