Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
PQ. Передача минимальной и максимальной даты в параметры., Необходим параметр, который заполняется автоматически на основании дат в таблице событий.
 
#1
10.04.2020 10:02:52
Здравствуйте!
Прошу подсказать решение задачи.

Есть столбец с датой. Необходимо найти наименьшую и наибольшую дату и создать из них параметры.
Эти параметры будут корректировать web-запрос.

Спасибо!

 
 
 
 
#2
10.04.2020 10:24:42
Код
Table.Min(звонки,"дата и время")[дата и время] meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=false]
Код
Table.Max(звонки,"дата и время")[дата и время] meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=false]
 
 
 
#3
10.04.2020 10:33:21
Цитата
ivanka написал:
Необходимо найти наименьшую и наибольшую дату и создать из них параметры. Эти параметры будут корректировать web-запрос.
если это всегда наименьшая и наибольшая даты, то нет смысла делать из них параметры. Достаточно просто посчитать их, как вы и сделали. Если же хотите менять их руками в вебе - тогда это же могут быть любые даты, не только наименьшая и наибольшая фактически? Иначе зачем бы их менять.
F1 творит чудеса
 
 
 
#4
10.04.2020 10:43:43
Цитата
Максим Зеленский написал:
тогда это же могут быть любые даты, не только наименьшая и наибольшая фактически?
Таблица в web наполняется данными между двумя датами. Мне необходимо в web-ссылку поместить 2 параметра (диапазон дат) и получать из web таблицу с диапазоном дат аналогичным диапазону дат в таблице событий.

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


 
Изменено: ivanka - 10.04.2020 11:05:30
 
 
 
#5
10.04.2020 10:48:04
Андрей Лящук, в самом параметре пусто ведь. Необходимо, чтобы в нём была минимальная или максимальная дата из таблицы событий.

 
Изменено: ivanka - 10.04.2020 10:48:22
 
 
 
#6
10.04.2020 10:50:39
Цитата
Максим Зеленский написал:
Достаточно просто посчитать их, как вы и сделали
А каким образом передать их в ссылку? Т.е. мне необходимо "параметризировать web-ссылку".
 
 
 
#7
10.04.2020 11:27:31
ivanka, поставьте сначала туда руками какие-то даты, а потом замените их в формуле на названия ваших запросов, которые считают мин и макс. Ну там надо убедиться, что передается в правильном виде. Возможно, нужно еще мин и макс преобразовать в правильный текстовый вид для включения в ссылку
F1 творит чудеса
 
 
 
#8
10.04.2020 12:23:21
Цитата
Максим Зеленский написал:
замените их в формуле на названия ваших запросов
Увы, при замене на запросы выдаёт ошибку: "Formula.Firewall: Запрос "web на запросах" (шаг "Data2") ссылается на другие запросы или этапы и поэтому не может напрямую обращаться к источнику данных. Измените эту комбинацию данных"

Код
let
    Источник = Web.Page(Web.Contents("https://www.cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=" & tmin & "&UniDbQuery.To=10.04.2020")),
    Data2 = Источник{2}[Data],
    #"Измененный тип" = Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",1),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные верхние строки", [PromoteAllScalars=true]),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Дата", type date}, {"Единиц", Int64.Type}, {"Курс", type number}})
in
    #"Измененный тип1"

Изменения производятся в самом хвосте ссылки:

.....br.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=" & tmin & "&UniDbQuery.To=10.04.2020

где " & tmin & " - я ссылаюсь на запрос. Аналогично в ссылке прописываются параметры и всё работает. Я пробовал ссылаться иначе. Не работает.

Я провал передавать в ссылку дату в формате текста и даты (есть в файле примера к этой записи).



Что я делаю не так? Может быть в "tmin" нужна какая-то кодировка?  
Изменено: ivanka - 10.04.2020 12:30:30
 
 
 
#9
10.04.2020 12:51:25
Цитата
ivanka написал:
Увы, при замене на запросы выдаёт ошибку:
эта ошибка многократно описана, описано, как ее разбирать, описаны обходные пути. Погуглите по "Formula.Firewall please rebuild this data combination"
Также хорошей практикой является вынесение параметров запроса в параметр Web.Contents RelativePath или Query.
Не могу это всё тут расписать, долго.
F1 творит чудеса
 
 
 
#10
10.04.2020 12:59:12
Цитата
Максим Зеленский написал:
Query
Вот это я изначально и хотел. Не нашёл как это сделать без функции.  
 
 
 
#11
10.04.2020 19:49:07
Максим Зеленский, ознакомился. Решение пока не для моего уровня знаний. И все же, можно ли из запроса сделать параметр, который сам заполняется? Или для это придётся создать функцию?

Под запросом я понимаю: подключился к таблице, в столбце дат нашёл наименьшую.  
Изменено: ivanka - 10.04.2020 19:49:53
 
 
 
#12
10.04.2020 20:28:56
Цитата
ivanka написал:
Под запросом я понимаю: подключился к таблице, в столбце дат нашёл наименьшую
А под параметром что понимаете?
Цитата
ivanka написал:
из запроса сделать параметр, который сам заполняется
Может так надо?
НА МЯСО В ПРОФИЛЕ
 
 
 
#13
10.04.2020 22:35:26
Михаил Л, c уровнем моих знаний, мне показалось, что вы привязали запрос к таблице excel, верно? Необходимо решение, которое будет работать в PBI.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="stats_tstats0119_2"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"дата и время", type date}}),
    Источник2 = Web.Page(Web.Contents("https://www.cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From="&Text.From(List.Min(#"Измененный тип"[дата и время]))&"&UniDbQuery.To="&Text.From(List.Max(#"Измененный тип"[дата и время])))),
    Data2 = Источник2{2}[Data],
    #"Измененный тип2" = Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип2",1),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные верхние строки", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Дата", type date}, {"Единиц", Int64.Type}, {"Курс", type number}})
in
    #"Changed Type"
Изменено: ivanka - 10.04.2020 22:35:52
 
 
 
#14
11.04.2020 05:38:24
Цитата
ivanka написал:
решение, которое будет работать в PBI
Откуда брать мин и макс?
НА МЯСО В ПРОФИЛЕ
 
 
 
#15
11.04.2020 11:50:47
ivanka,
Вы от чего желаете получить минимальную дату? Если от ресурса ЦБ, так там самая первая дата 01.07.1992
 
 
 
#16
11.04.2020 18:36:58
Цитата
DrillPipe написал:
Вы от чего желаете получить минимальную дату?
Цитата
Михаил Л написал:
Откуда брать мин и макс?

Господа, я к самой задаче приложил файл примера. В нём уже были найдены минимальная и максимальная дата. В запросе видно, что даты берутся из таблицы "звонки."

DrillPipe, Михаил Л, Максим Зеленский,  вчера просидев до ночи, я взял за основу решение
Цитата
Михаил Л написал:
Может так надо?

Первое. Я нашёл минимальную и максимальную дату. Перевёл формат даты в текст. Из таблиц сделал листы.
Второе. Я указал эти листы в web запросе через: From=" &Text.From(tmin)& "&UniDbQuery.To= &Text.From(tmax)")),

В целом web-запрос получился таким:
Код
let
    Источник = Web.Page(Web.Contents("https://www.cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&UniDbQuery.date_req1=&UniDbQuery.date_req2=&UniDbQuery.VAL_NM_RQ=R01235&UniDbQuery.From=" &Text.From(tmin)& "&UniDbQuery.To= &Text.From(tmax)")),
    Data2 = Источник{2}[Data],
    #"Измененный тип" = Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",1),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные верхние строки", [PromoteAllScalars=true]),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Дата", type date}, {"Единиц", Int64.Type}, {"Курс", type number}}),
    #"Сортированные строки" = Table.Sort(#"Измененный тип1",{{"Дата", Order.Descending}})
in
    #"Сортированные строки"

Запрос работает. Но есть НЮАНС!)

В файле, который я прикладываю есть 2 запроса на которые стоит обратить внимание.
1. Лист "источник" - тут находятся даты (min и max), которые определяются и передаются в web запрос.
2. Лист "web на запросах" - результат запроса, в который поступают min и max даты из "источник". Этот запрос обращается по модифицированной URL в ЦБ и возвращает таблицу с датами в диапазоне дат min и max на лист "web на запросах".

Когда, в таблицу источник добавляется новая max дата, то "web на запросах" увеличатся до этой даты.
Когда, в таблице "источник" max дата уменьшается (просто удалили пару строк), то  "web на запросах" не уменьшается. Он остаётся таким, каким был.
Как это исправить?

Если посмотреть на "web на параметрах", то он работает как надо. Разве что приходится руками забивать актуальные даты.
На рисунке можно увидеть, что максимальная дата в "источник" = 23.02.2019 (E3). Следовательно, в "web на запросах" должна быть аналогичная дата, однако она другая = 11.04.2020 (E2). Просто я в "источник" указал строку с датой 11.04.2020, обновил запрос, таблица "web на запросах" увеличилась до 11.04.2020. Я зашёл в источник и удалил строку с датой 11.04.2020, но "web на запросах" меньше уже не стал.

Надеюсь, что я достаточно подробно передал ситуацию.
Как это исправить?

 
Изменено: ivanka - 11.04.2020 18:41:28
 
 
 
#17
11.04.2020 20:06:38
ivanka,у вас кавычка стоит не в том месте :)

Видите, первая дата черная, вторая - красная, потому что в кавычках.
F1 творит чудеса
 
 
 
#18
11.04.2020 20:20:03
Цитата
Максим Зеленский написал:
Видите
Максим, Максим Зеленский может использовать Excel 2016 - там нет подсветки синтаксиса.
 
 
 
#19
11.04.2020 20:51:21
ivanka,
Код
let
    ExcelSource = Excel.CurrentWorkbook(){[Name="stats_tstats0119_2"]},
    GetDateMin = Date.ToText(Date.From(List.Min(ExcelSource[Content][дата и время]))),
    GetDateMax = Date.ToText(Date.From(List.Max(ExcelSource[Content][дата и время]))),
    Source = Xml.Tables(Web.Contents("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=" & GetDateMin & "&date_req2=" & GetDateMax & "&VAL_NM_RQ=R01235")),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Record", {"Nominal", "Value", "Attribute:Date", "Attribute:Id"}, {"Nominal", "Value", "Attribute:Date", "Attribute:Id.1"})
in
    #"Expanded {0}"
Изменено: DrillPipe - 11.04.2020 21:01:04
 
 
 
#20
11.04.2020 21:22:36
Цитата
DrillPipe написал:
ExcelSource = Excel.CurrentWorkbook(){[Name="stats_tstats0119_2"]},

Благодарю вас за помощь, попробую адаптировать.
Если возьму и при прочих равных скопирую этот код в Power BI, он будет работать?
Вы что, ссылку ЦБ модифицировали?
Изменено: ivanka - 11.04.2020 21:31:11
 
 
 
#21
11.04.2020 21:28:52
Андрей VG, Максим Зеленский,
Цитата
Максим Зеленский написал:
   #"Expanded {0}"
У меня нет подсветки в Excel 2019 и данная строка хорошо работает с min датой. Но при переносе кода в PBI подсветка показывает то, что видно у вас на скриншоте
В PBI данная строка не работает. Какие кавычки необходимо убрать? Я убрал их так, чтобы вся ссылка была красной, а потом менял так, чтобы 2 куска были чёрными, но запрос перестаёт хоть как-то работать. Я перепробовал уже комбинаций 7. Они не работают.
Код
R01235&UniDbQuery.From=Text.From(tmin)&UniDbQuery.To= &Text.From(tmax)"
 
Изменено: ivanka - 11.04.2020 22:25:04
 
 
 
#22
11.04.2020 21:46:02
Цитата
ivanka написал:
Вы что, ссылку ЦБ модифицировали?
Да. Эта ссылка получает ответ от ЦБ в формате XML. Вы все равно в своем запросе зачищаете данные полученные как HTML страница

Касательно переноса - из какого источника вы планируете получать данные "звонки" в PBI?
По вашей схеме вы получаете данные с Листа ИСТОЧНИК, а потом уже tmax - tmin
 
 
 
#23
11.04.2020 22:06:41
Цитата
ivanka написал:
Если возьму и при прочих равных скопирую этот код в Power BI, он будет работать?

С изменениями под PBI. У меня работает.
Код
let
    ExcelSource = Excel.Workbook(File.Contents("ПУТЬ К ФАЙЛУ\пример запросов_DrillPipe.xlsx"), null, true){[Item="stats_tstats0119_2",Kind="Table"]}[Data],
    GetDateMin = Date.ToText(Date.From(List.Min(ExcelSource[дата и время]))),
    GetDateMax = Date.ToText(Date.From(List.Max(ExcelSource[дата и время]))),
    Source = Xml.Tables(Web.Contents("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=" & GetDateMin & "&date_req2=" & GetDateMax & "&VAL_NM_RQ=R01235")),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Record", {"Nominal", "Value", "Attribute:Date", "Attribute:Id"}, {"Nominal", "Value", "Attribute:Date", "Attribute:Id.1"})
in
    #"Expanded {0}"
 
 
 
#24
12.04.2020 17:37:32
DrillPipe, я правильно понимаю, что это решение не работает, если подключение будет Web.Page(Web.Contents, а не через Xml.Tables(Web.Contents ?
 
 
 
#25
12.04.2020 17:55:46
Цитата
ivanka написал:
я правильно понимаю, что это решение не работает
Работает.
Код
let
    ExcelSource = Excel.Workbook(File.Contents("Путь к файлй\пример запросов_DrillPipe.xlsx"), null, true){[Item="stats_tstats0119_2",Kind="Table"]}[Data],
    GetDateMin = Date.ToText(Date.From(List.Min(ExcelSource[дата и время]))),
    GetDateMax = Date.ToText(Date.From(List.Max(ExcelSource[дата и время]))),
    Источник = Web.Page(Web.Contents("https://www.cbr.ru/currency_base/dynamics/?UniDbQuery.Posted=True&UniDbQuery.mode=1&...; & GetDateMin & "&UniDbQuery.To="&GetDateMax)),
    Data2 = Источник{2}[Data],
    #"Измененный тип" = Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",1),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные верхние строки", [PromoteAllScalars=true]),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Дата", type date}, {"Единиц", Int64.Type}, {"Курс", type number}}),
    #"Сортированные строки" = Table.Sort(#"Измененный тип1",{{"Дата", Order.Descending}})
in
    #"Сортированные строки"
Изменено: DrillPipe - 12.04.2020 17:57:19
 
 
 
#26
12.04.2020 18:12:19
Цитата
ivanka написал:
c уровнем моих знаний, мне показалось, что вы привязали запрос к таблице excel, верно? Необходимо решение, которое будет работать в PBI.Код ?

let    Источник = Excel.CurrentWorkbook(){[Name="stats_tstats0119_2"]}[Content],  
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"дата и время", type date}}),  
Чтобы отвязать замените Excel.CurrentWorkbook(){[Name="stats_tstats0119_2"]}[Content] на date
НА МЯСО В ПРОФИЛЕ
 
 
 
#27
12.04.2020 18:35:07
Цитата
DrillPipe написал:
Работает.
Что-то не работает. С кавычками что-то. Я их переставил, но всё равно слетает. Я что-то не пойму как правильно ставить кавычки в случае с URL.  
 
 
 
#28
12.04.2020 18:47:43
В каком виде вам возвращаются даты GetDateMin и GetDateMax?

Код
"https://www.cbr.ru/currency_base/dynamics/?" &
"UniDbQuery.Posted=True" &
"&UniDbQuery.mode=1" & 
"&UniDbQuery.date_req1=" &
"&UniDbQuery.date_req2=" &
"&UniDbQuery.VAL_NM_RQ=R01235" &
"&UniDbQuery.From=" & 
GetDateMin & 
"&UniDbQuery.To=" & 
GetDateMax
Изменено: DrillPipe - 12.04.2020 18:57:27
 
 
 
#29
12.04.2020 19:00:42
МОДЕРАТОРЫ ПРОШУ ПРОЩЕНИЯ ЗА ОФТОП

ivanka,
Вы пробовали мой вариант XML? Вы получите тот-же результат что и HTML -  только меньше телодвижений будет.
Посмотрите на свой запрос, что вы делаете на каждом шаге
1. Получаете страницу с разметкой
2. Находите блок содержащий таблицу
3. Обрабатываете ее - удаляете не нужные строки и столбцы

А теперь наберите в Хроме строку
"http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=01/04/2020&date_req2=11/04/2020&VAL_NM_RQ=R01235"

Вы получили результат без всех разметок HTML
 
 
 
#30
12.04.2020 19:02:08
DrillPipe, Михаил Л, я благодарю вас за помощь! Жму крепко руки! Благодарю!

Код
let
    ExcelSource = date,
    GetDateMin = Date.ToText(Date.From(List.Min(ExcelSource[Дата]))),
    GetDateMax = Date.ToText(Date.From(List.Max(ExcelSource[Дата]))),
    
    Источник = Web.Page(Web.Contents(
        "https://www.cbr.ru/currency_base/dynamics/?" &
        "UniDbQuery.Posted=True" &
        "&UniDbQuery.mode=1" & 
        "&UniDbQuery.date_req1=" &
        "&UniDbQuery.date_req2=" &
        "&UniDbQuery.VAL_NM_RQ=R01235" &
        "&UniDbQuery.From=" & 
        GetDateMin & 
        "&UniDbQuery.To=" & 
        GetDateMax)),

    Data2 = Источник{2}[Data],
    #"Измененный тип" = Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",1),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные верхние строки", [PromoteAllScalars=true]),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Дата", type date}, {"Единиц", Int64.Type}, {"Курс", type number}}),
    #"Сортированные строки" = Table.Sort(#"Измененный тип1",{{"Дата", Order.Descending}})
in
    #"Сортированные строки"

Изменено: ivanka - 12.04.2020 19:02:45
 
 
 
Страницы: 1 2 След.
Наверх