Страницы: 1
RSS
PQ. База данных в Power Query (таблица с историей)., Таблица, которая дополняется новыми данными, но НЕ перезаписывает полученные ранее
 
Здравствуйте!
Прошу помочь с решением задачи.

Представим, что не существует баз данных, и задачу необходимо решить через PQ (для Power BI).
Условия задачки:
Есть сайт с прогнозом погоды. Он выдаёт прогноз по дням, без даты (Источник данных.xlsx).
Наш файл (Пример. БД в PQ.xlsx) подключается к источнику 2 раза в день. Прогноз погоды уточняется 2 раза в день и может измениться.

Подключились в первый раз:


Подключились второй раз:


Поменялся прогноз за 2 дня. Однако, нам необходимо увидеть динамику изменений. Для этого, необходимо, чтобы в таблице не удалялись предыдущие значения и фиксировалась дата соответствующая "Сегодня":



Функцию DateTime.ToRecord () я использовал для определения времени обновления отчёта.
Функцией DateTime.FixedLocalNow () я рассчитывал зафиксировать время.

Как сделать остальное - загадка.

Благодарю!

P.S.: Почему-то представляю 2 таблицы. Одна содержит только актуальное обновление. Вторая, обращается к первой и забирает данные для фиксирования.
Возможно, тут потребуется некоторая последовательность запуска обновления, но это уже другой вопрос.  
Изменено: ivanka - 16.08.2020 20:51:09
 
Наброски решения, но через Excel: https://blog.jamesbayley.com/2018/04/23/power-query-how-to-load-only-fresh-rows-and-create-an-excel-history-table/
 
Доброе время суток.
Цитата
Таблица, которая дополняется новыми данными,
Почитайте Self Referencing Tables in Power Query. В прочем, по self reference решений и на нашем форуме уже более чем достаточно.
 
Андрей VG, здравствуйте! Благодарю!
 
Цитата
ivanka написал:
задачу необходимо решить через PQ (для Power BI)
Учитывая вышенаписанное читайте тут и тут.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Учитывая
Алексей, а вот этот-то момент я и пропустил, глядя только на файлы. Не думаю, что у ТС есть Pro Account, а в обычном Power BI, насколько помню - нет пока подгрузки данных, или уже появилось?
 
Цитата
Андрей VG написал:
или уже появилось?
Это скорее к Максиму. Я им пользуюсь эпизодически, если от кого заказ подвалит. В повседневной работе - нет. Но в любом случае вряд ли такой пряник МС отдаст в бесплатное пользование. Не верю я в бесплатный сыр.
Вот горшок пустой, он предмет простой...
 
Цитата
Андрей VG написал:
Не думаю, что у ТС есть Pro Account
ТС - это....?
Функционал Power BI Pro я готов рассматривать.

Цитата
Андрей VG написал:
в обычном Power BI, насколько помню - нет пока подгрузки данных
PooHkrd, Андрей VG, статья на сайте Microsoft от 22.06.2020: "Добавочное обновление теперь доступно для Power BI Pro" https://docs.microsoft.com/ru-ru/power-bi/admin/service-premium-incremental-refresh.  
 
Цитата
PooHkrd написал:
Учитывая вышенаписанное читайте  тут  и  тут .
Насколько я понимаю, "добавочное обновление" подразумевает наличие столбца с датой в источнике. В моей задаче такого столбца нет. Есть только статусы. Т.о. я считаю, что в моей задаче таблицу с "историческими данными" необходимо наполнять не посредством инкрементного обновления, где смысл сводится к тому, что бы не забирать данные, которые уже есть в таблице, а к алгоритму, который фиксирует, "делает снимки" данных (без дат) и фиксирует в таблице когда этот "снимок" был сделан.
Изменено: ivanka - 17.08.2020 09:51:32
 
В Power BI (Pro) инкрементное обновление уже включили, да. Но важно иметь в виду, что оно работает именно после публикации в Service - то есть данные накапливаются и хранятся именно там.

Насчет даты в источнике - ее можно симулировать, как приведено в примере с суррогатным ключом в документации. Если дадите свой "погодный сайт", то можно поэкспериментировать.
F1 творит чудеса
 
Цитата
ivanka написал:
который фиксирует, "делает снимки" данных (без дат) и фиксирует в таблице когда этот "снимок" был сделан.
Ну, в Excel это так.
Предполагается, что Источник данных.xlsx в папке c:\Path
Изменено: Андрей VG - 17.08.2020 10:47:22
 
Андрей VG, работает! Андрей, благодарю Вас за решение.

Я думал, что код будет содержать функции объединения таблицы, какие-то элементы на сохранение предыдущего содержимого, что-то вроде:
  1. PQ подключается к источнику. Создаёт таблицу с датами и значениями = "Временная таблица".
  2. Вторая часть запроса проверяет "Временную таблицу" с Историческими данными по столбцу "дата" и если не находит совпадений (не находит 100%), то добавляет данные из временной таблицы в таблицу с Историческими данными.
Выходит, что
Код
result = Source & addLoadDateTime,
не позволяет таблице перезаписывать данные, так?

Я в восторге!!!
Изменено: ivanka - 17.08.2020 12:12:13
 
Цитата
Максим Зеленский написал:
Если дадите свой "погодный сайт", то можно поэкспериментировать.
Максим, такого сайта нет. Мне руководство озвучило гипотетическую ситуацию (звучала посложнее истории с сайтом погоды), которую я интерпретировал в таком вот виде. Если честно, то я и сам давно задавался вопросом о том, как создать таблицу с историей внутри Power BI.
Изменено: ivanka - 17.08.2020 11:41:08
 
Цитата
ivanka написал:
не позволяет таблице перезаписывать данные, так?
Если формально, то не совсем так. Данные в любом случае перезаписываются, но такое объединение позволяет добавлять к результату исторические данные. Просто в Экселе запрос может читать данные из таблицы, в которую он выводит результат.
Вот горшок пустой, он предмет простой...
 
PooHkrd, да, с Power BI не работает.
Изменено: ivanka - 17.08.2020 11:49:19
 
Цитата
ivanka написал:
Я в восторге!!!
Судя по вашему описанию - вы ничего не поняли.
1. Любое подключение, созданное на базе Power Query, выводимое на рабочий лист формирует таблицу.
2. Эта таблица может участвовать в любом запросе Power Query
3. Power Query сначала начитывает данные, обрабатывает и только в самом конце формирует результат (ровно также себя ведут и SQL подключения).
На этом и формируется self-reference - на SQL подключениях я использовал это в году так 2010 уже и подозреваю, что этому приёму уже лет этак 20.
Что делает код в Пример. БД в PQ.xlsx
1. Считывает таблицу с именем подключения, которое выводит в эту таблицу результат :)
2. Считывает внешнюю таблицу
3. К внешней таблицы добавляет столбец текущего времени и даты
4. Добавляет внешнюю таблицу со столбцом даты времени в хвост считанной таблицы.
После чего Excel загружает результат на лист. Таким образом
Цитата
ivanka написал:
и если не находит совпадений (не находит 100%)
нигде не выполняется.
Цитата
ivanka написал:
не позволяет таблице перезаписывать данные, так?
что значит не позволяет? Это как раз пункт 4.
 
Цитата
Андрей VG написал:
Судя по вашему описанию - вы ничего не поняли.
Да, я не понял, что данное решение завязано на таблицу в Excel. В любом случае, оно для меня полезно.
Но требуется решение под Power BI.  
 
Цитата
ivanka написал:
Но требуется решение под Power BI.  
Так его вам описал Максим. Точно также подключаетесь к таблице внешней книги (иле ещё чего там). Добавляете столбец текущей даты времени для строк источника. Конфигурируете инкрементальную загрузку для этого запроса с учётом.
Цитата
Максим Зеленский написал:
Но важно иметь в виду, что оно работает именно после публикации в Service - то есть данные накапливаются и хранятся именно там
Каждый раз при обновлении данных этой таблицы будут добавлены строки из внешней таблицы с новыми датами.
 
Цитата
Андрей VG написал:
Конфигурируете инкрементальную загрузку для этого запроса с учётом.
Андрей, Вы считаете, что для Power BI эту задачу можно решить только через инкрементальную загрузку? Это действительно единственный возможный вариант?
Каким-то образом в PQ можно эмитировать записанную таблицу? Т.е. чтобы запрос считывал её как таблицу в Excel (как в Вашем решении).  
Изменено: ivanka - 17.08.2020 15:54:20
 
вам, кажется, уже три варианта предложили. Можете через потоки сохранять. Можете через инкрементальную загрузку. Чем не устраивает?
Цитата
ivanka написал:
чтобы запрос считывал её как таблицу в Exce
Чтобы считывал - без проблем. Чтобы дописывал в нее же - ТОЛЬКО ЧЕРЕЗ ИНКРЕМЕНТАЛЬНУЮ ЗАГРУЗКУ
F1 творит чудеса
 
ivanka, отвечу за Андрея - да. Причем, подозреваю, что в отличие от Экселя в PBI не получится организовать, например, сравнение полученных данных с тем, что уже имеется в модели. Можно только доливать в модель новое. Но, возможно, Максим меня поправит.
Вот горшок пустой, он предмет простой...
 
PooHkrd, Максим Зеленский, благодарю!
 
ivanka, а присобачить?
Правда, сам не пробовал и не знаю подойдет ли для решения проблемы
 
Цитата
Максим Зеленский написал:
Если дадите свой "погодный сайт", то можно поэкспериментировать.
Здравствуйте!
Создал запрос, который берёт прогноз погоды и добавляет к нему дату.
Создал поток данных. Добавил код в запрос. Поток данных запрос принял. Настроил параметры инкрементного обновления. И, казалось, что дело в шляпе, но... PBI Service выдал следующее: "Этот поток данных содержит сущности, для обновления которых требуется емкость Premium, в том числе связанные сущности, вычисляемые сущности и сущности с действующими параметрами добавочного обновления."

Можно это обойти? Я просто не пойму на что он ругается? (сейчас полетят камни...)

Запрос простой:
Код
let
    Источник = Text.FromBinary(Web.Contents("https://www.gismeteo.ru/weather-sankt-peterburg-4079/now/")),
    #"Извлеченная таблица из HTML" = Html.Table(Источник, {{"Column1", ".meteostation__title"}, {"Column2", ".__frame .unit_temperature_c"}, {"Column3", ".__frame .unit_temperature_f"}, {"Column4", ".unit_wind_m_s .meteostation__measure"}, {"Column5", ".unit_wind_mi_h .meteostation__measure"}, {"Column6", ".unit_wind_km_h .meteostation__measure"}}, [RowSelector=".meteostation"]),
    SourceTable = Table.SelectRows(#"Извлеченная таблица из HTML", each ([Column1] = "Санкт-Петербург (Пулково)")),
    #"Другие удаленные столбцы" = Table.SelectColumns(SourceTable,{"Column1", "Column2"}),
    AddDataTimeColumn = Table.AddColumn(#"Другие удаленные столбцы", "DateTimeUpdate", each DateTime.FixedLocalNow()),
    HistoryTable = AddDataTimeColumn,
    #"Вставленный текст после разделителя" = Table.AddColumn(HistoryTable, "Текст после разделителя", each Text.AfterDelimiter([Column2], "+"), type text),
    #"Переименованные столбцы" = Table.RenameColumns(#"Вставленный текст после разделителя",{{"DateTimeUpdate", "ДатаВремя"}, {"Column1", "Город"}, {"Column2", "+Прогноз"}, {"Текст после разделителя", "Градусы"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"ДатаВремя", "Город", "+Прогноз", "Градусы"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Переупорядоченные столбцы",{{"ДатаВремя", type datetime}, {"Градусы", type number}})
in
    #"Измененный тип"



Настройки обновления:


После попытки обновить данные, PBI Service, в настройках запроса добавил 2 функции:
Код
let
  RangeStart = #datetime(0001,01,01,00,00,00) meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]
in
  RangeStart


и
Код
let
  RangeEnd = #datetime(9999,12,31,23,59,59) meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]
in
  RangeEnd



А сам запрос PBI Service поправил следующим образом:
Код
let
  Источник = Text.FromBinary(Web.Contents("https://www.gismeteo.ru/weather-sankt-peterburg-4079/now/")),
  #"Извлеченная таблица из HTML" = Html.Table(Источник, {{"Column1", ".meteostation__title"}, {"Column2", ".__frame .unit_temperature_c"}, {"Column3", ".__frame .unit_temperature_f"}, {"Column4", ".unit_wind_m_s .meteostation__measure"}, {"Column5", ".unit_wind_mi_h .meteostation__measure"}, {"Column6", ".unit_wind_km_h .meteostation__measure"}}, [RowSelector = ".meteostation"]),
  SourceTable = Table.SelectRows(#"Извлеченная таблица из HTML", each [Column1] = "Санкт-Петербург (Пулково)"),
  #"Другие удаленные столбцы" = Table.SelectColumns(SourceTable, {"Column1", "Column2"}),
  AddDataTimeColumn = Table.AddColumn(#"Другие удаленные столбцы", "DateTimeUpdate", each DateTime.FixedLocalNow()),
  HistoryTable = AddDataTimeColumn,
  #"Вставленный текст после разделителя" = Table.AddColumn(HistoryTable, "Текст после разделителя", each Text.AfterDelimiter([Column2], "+"), type text),
  #"Переименованные столбцы" = Table.RenameColumns(#"Вставленный текст после разделителя", {{"DateTimeUpdate", "ДатаВремя"}, {"Column1", "Город"}, {"Column2", "+Прогноз"}, {"Текст после разделителя", "Градусы"}}),
  #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы", {"ДатаВремя", "Город", "+Прогноз", "Градусы"}),
  #"Измененный тип" = Table.TransformColumnTypes(#"Переупорядоченные столбцы", {{"ДатаВремя", type datetime}, {"Градусы", type number}}),
  #"ПрогнозПогоды-D094D0B0D182D0B0D092D180D0B5D0BCD18F-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Измененный тип", each DateTime.From([ДатаВремя]) >= RangeStart and DateTime.From([ДатаВремя]) < RangeEnd)
in
  #"ПрогнозПогоды-D094D0B0D182D0B0D092D180D0B5D0BCD18F-autogenerated_for_incremental_refresh"



Благодарю!
Изменено: ivanka - 24.08.2020 20:01:03
 
Проблема не в запросе. Для возможности обновления данных требуется премиум подписка/workspace
Увидеть премиум легко - возле наименования workspace стоит иконка ограненый брильянт
 
Цитата
DrillPipe написал:
Для возможности обновления данных требуется премиум подписка
Учитывая:

Цитата
ivanka написал:
статья на сайте Microsoft от 22.06.2020: "Добавочное обновление теперь доступно для Power BI Pro"  https://docs.microsoft.com/ru-ru/power-bi/admin/service-premium-incremental-refresh .  

Делаю вывод, что Добавочная загрузка через Потоки данных - это для небожителей.
Добавочная загрузка через файл PBI - доступно на Pro. Верно?
 
Скорее так

https://docs.microsoft.com/ru-ru/power-bi/admin/service-premium-what-is
 
Цитата
ivanka написал:
Добавочная загрузка через Потоки данных
В потоках пока только Премиум, да. Но если источник облачный, читайте по ссылкам в #5
Цитата
ivanka написал:
Добавочная загрузка через файл PBI - доступно на Pro. Верно?
да, но не через файл, а через датасет, опубликованный в сервисе.
Насчет
Цитата
ivanka написал:
озвучило гипотетическую ситуацию
ну, получаете гипотетические ответы.
F1 творит чудеса
Страницы: 1
Наверх