Страницы: 1
RSS
Преобразование времени в число Power Query
 
Добрый день! Коллеги, нужна ваша помощь с Power Query. Необходимо преобразовать время из формата времени в числовой формат. Во файле-примере есть 3 столбца
time = это значение времени, которое необходимо преобразовать (формат из корпоративной отчетности)
37:30:55 = это то же самое время, но в формате Excel "37:30:55"
number = это итоговый результат, который нужно получить с помощью PQ
Вроде все просто, открываем PQ и просто умножаем time на 24, можно даже тип данных не менять и полученное значение совпадет с эталонным из столбца number. Это я и сделал в запросе "Эта книга".

Однако, если открыть PQ не в этом же файле а сослаться на него через "Из файла" -> "Из Excel" (запрос "Из файла"), то формат столбцов time и 37:30:55 будет отличаться от формата из запроса Эта книга. Просто так умножить значение этих столбов на 24 мы больше не можем. Для умножения необходимо менять тип данных. Я изменил тип данных в столбце time на тип number, а в столбце 37:30:55 на тип time. Но после умножения на 24 ни один из получившихся результатов не совпал с эталоном из столбца number. Возможно это баг, либо я чего то не понимаю. Подскажите пожалуйста как правильно произвести преобразование в запросе "Из файла"?
 
Murderface_, в последний шаг попробуйте добавить -24
 
Можно так:
Код
let
    source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="source"]}[Content]{0}[address]), null, true){[Item="Таблица1",Kind="Table"]}[Data],
    changeType = Table.TransformColumnTypes(source,{{"time", type number}, {"37:30:55", type datetime}}),
    Time = Table.TransformColumns(changeType, {{"37:30:55", each 24 * Number.From( _ - DateTime.From(1) ), type number}})
in
    Time
Изменено: PooHkrd - 13.02.2020 10:44:55
Вот горшок пустой, он предмет простой...
 
abc1, PooHkrd, спасибо!
 
Я вот все равно не понимаю, почему при запуске PQ из книги и из файла форматы различаются.
 
Это ж 2 разных коннектора, один видит именованную таблицу в оперативке и читает данные оттуда (файл же уже загружен в неё целиком). Второй ковыряется в xml структуре и извлекает данные читая их с диска. Представление данных в оперативке и файле на диске могут отличаться. Тем более что в PQ представление типа time отличается от такого в Экселе, а типа duration в Экселе нет совсем. Поэтому вот такие накладки.
Изменено: PooHkrd - 13.02.2020 11:42:41
Вот горшок пустой, он предмет простой...
 
Коллеги, добрый день! Благодаря рекомендациям из этой темы я успешно работал в PQ с форматом времени Excel "37:30:55". При преобразовании форматов, достаточно было вычесть 24 часа и данные из PQ начинали совпадать с данными из Excel. Однако сегодня обнаружил ситуацию, когда часть данных в формате "37:30:55" не требует преобразования для PQ. Я не могу понять почему так происходит?

В примере 2 листа и 2 запроса PQ на эти листы. В запросе "Время с преобразованием" данные между PQ и EXCEL различаются и их можно преобразовать, с помощью описанного в теме решения. Но в запросе "Время без преобразования" часть данных совпадает с требуемым результатом без преобразований. Итоговые значения данного запроса будут отличаться от фактических данных. Подскажите пожалуйста что тут не так?
 
Так у вас там разница начинается на 7 знаке после запятой. Такая точность вполне себе позволяет достигать одинаковых значений с точностью до секунды. Вот на сотых или даже тысячных долях секунды уже будут различия. Оно вам надо? Если нет, то округляйте Number.RoundUp( time, 6) и будет вам щястье. Если же вы сверяете время спуска по трассе бобслеистов/саночников, то там конечно да, это может повлечь драматический результат.  ;)
Вот горшок пустой, он предмет простой...
 
PooHkrd, подскажите пожалуйста почему так происходит? В запросе "Время с преобразованием" данные между числом и временем в формате Excel "37:30:55" различаются ровно на 1, но в запросе "Время без преобразования" происходит такое колдунство? На обоих листах формат времени в ячейках установлен "37:30:55".

Причем в запросе "Время без преобразования" есть даже 3 разные ситуации, когда время преобразованное в число совпадает с эталонным значением, когда различается на 1, и когда различается на 0,00000001.
Изменено: Murderface_ - 14.04.2020 10:40:43
 
Excel считает, что 0-й день - это 0-е января 1900 года, а PQ - что это 30 декабря 1899.
Плюс к тому в Excel 1900 год високосный, а в PQ - нет.

Поэтому номер дня в Excel и в PQ начинает совпадать только с 61-го дня, т.е. с 1 марта 1900 года. Отсортируйте ваше время по возрастанию и поймете, в чем прикол.
Различия на мелкие значения происходят, как правило, в 10-м знаке после запятой у секунд и связано с округлениями, числом с плавающей точкой и т.д.
 
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Поэтому номер дня в Excel и в PQ начинает совпадать только с 61-го дня, т.е. с 1 марта 1900 года.
Вот это поворот!  8-0  
И ведь вроде в одной корпорации люди работают и к одному и тому же продукту все это прикручивали. Все таки во всех больших компаниях такого бардака навалом, когда в соседнем кабинете не знают что люди делают или когда-то сделали.
Вот горшок пустой, он предмет простой...
 
Максим Зеленский, это невероятно. Спасибо за ответ. Никогда бы не подумал, что это так устроено. В таком случае остается открытым вопрос как правильно работать с данными Excel в формате "37:30:55" с помощью PQ?
 
Что типа такого:
Код
= if time < 1 марта 1900 года then вариант1 else вариант2
Вот горшок пустой, он предмет простой...
 
Цитата
Murderface_ написал:
правильно работать с данными Excel в формате "37:30:55" с помощью PQ?
а что значит - правильно? Какой результат нужен? Имея на руках формат времени PQ, получить числовой формат, равный числовому формату Excel? или что-то другое? я немного запутался, хоть и прочитал с начала
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Имея на руках формат времени PQ, получить числовой формат, равный числовому формату Excel
Максим, вы все правильно поняли.
 
ммм... в часах если:
Код
24*Number.From([time]) - (if Duration.TotalDays([time]-DateTime.From(0))>=61 then 0 else 1)

если в днях, убираем умножение на 24
F1 творит чудеса
 
Максим Зеленский, спасибо!
 
Коллеги добрый день! Я кажется нашел исключение из правил. Посмотрите пожалуйста файл во вложении. В запросе PQ из примера 2 строки в формате "37:30:55":

01.03.1900 0:39:59 в Excel это число 61,02776045

01.03.1900 21:11:24 в Excel это число 60,8829143

Таким образом во второй строке время больше, а число меньше. Хотя должно быть наоборот. Я не понимаю что происходит. В данном примере решение Максима не работает  :(  
Изменено: Murderface_ - 15.04.2020 09:13:16
 
Murderface_, кажется вы заработались. В примере все корректно:
Вот горшок пустой, он предмет простой...
 
PooHkrd, а вы откройте запрос, там даты другие будут.
 
Murderface_, гы.  :D Подстава какая-то.
Это я вас не правильно понял сначала, значит.
Вот горшок пустой, он предмет простой...
 
PooHkrd, я тоже офигел как увидел  :D  Осталось придумать что с этим делать)
 
Murderface_, дык это баг, чего с ним еще делать? На МС писать. Переделаю ваш файл и тисну запрос на технете, даже самому интересно. Ссылку скину.
Вот горшок пустой, он предмет простой...
 
PooHkrd, спасибо)
 
Вот темка
Вот горшок пустой, он предмет простой...
 
Там действительно забавно получается.
Я, когда писал сообщение выше, считал, похоже, из CurrentWorkbook
Вот как если сравнивать:

Описывается это так:
Слева исходная таблица в Excel.
Справа сведение в одну двух таблиц, через Excel.CurrentWorkbook (далее WB) и через Excel.Workbook (далее File), в обоих случаях берем данные из таблицы, а не с листа.
Данные брали в таблицу, форматирование столбца As long time в формат datetime (при получении из файла он уже сразу такой, а при получении из WB там будет просто число, как в первом столбце), далее обратно из datetime при помощи Number.From переводили в число.
После выгрузки данные в столбцах As long time форматировались в [чч]:мм:сс

Желтым выделены расхождения с Excel.
  1. (Оранжевый прямоугольник)
    При получении из WB полученное числовое отображение как ни странно, совпадает с Excel, а вот datetime сползает на -1 до 29 февраля 1900 включительно. Начиная с 1 марта 1900 выравнивается.
  2. (Красный прямоугольник)
    При импорте из файла как я писал выше, "длинное время" у нас уже сразу datetime, и там встречается сразу два 1 марта, и одно из них - вместо 29 февраля.

    Но все остальные "длинные времена" - такие же как в Excel, удивительно...
  3. (Зеленый прямоугольник)
    При импорте из файла числовое отображение длинного времени сдвигается относительно Excel на 1, вплоть до 29 февраля 1900 (по Excel времени). То есть, есть два 61-х дня, если можно так выразиться.
ИТОГО
При получении из файла:
если у нас в распоряжении только длинное время, "все пропало" в районе 61 дня:
  • Если числовое отображение <61, или датавремя <29.03.1900, можно брать Number.From от "длинного времени" и вычитать 1, получим такое же число, как в Excel.
  • Если числовое отображение >=62, или датавремя >=02.03.1900, то числовое отображение совпадает.
  • Если числовое отображение >=61 и <62, то есть датавремя 29.03.1900 или 01.03.1900 в Excel, то мы теряем весь кусок данных, который в Excel отвечает за 29 февраля 1900 - у нас два 1 марта, и мы не знаем, какое из них настоящее, соответственно, не можем восстановить правильное число - то ли вычитать 1, то ли нет.
При получении из текущей книги:
  • Если в исходной таблице на листе будет выбран формат отображения "длинное время", в PQ оно будет получено в виде числа, совпадающего с числовым значением в Excel.
  • Если мы преобразуем это в датавремя, то получим визуальный сдвиг, и для того, чтобы после выгрузки в Excel можно было "датавремя" перевести в "длинное время" - такое же, как в Excel, нужно добавлять 1 к датам между 1 января 1900 и 28 февраля 1900 включительно. Звучит дико, но по картинке (п.1) понятно, о чем речь
От такой от бардак :) Из CurrentWorkbook надежнее, а вот при получении из файла - шняга :(
Изменено: Максим Зеленский - 15.04.2020 17:16:00
F1 творит чудеса
 
Файл к предыдущему сообщению.
PS при импорте из файла без разницы, откуда берем "длинное время" - из таблицы или с листа, в обоих случаях сразу распознается как datetime.

Так что спасибо за находку :) позже дополню тему на течнете.
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
дополню тему на течнете
Спасибо Максим.
 
Цитата
Максим Зеленский написал:
Currentorkbook надежнее
Вот! Еще одна монетка в копилку того, что эта функция берет значения из оперативки и поэтому корректно забирается именно числовое значение. Я, кстати, и на эту тему вопросик уже задал.
Изменено: PooHkrd - 15.04.2020 17:02:46
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх