How to create parameter for dynamic date parameter in json api

[Anonymous]

Hi all,

still pretty new to Power BI and trying to find out if an idea is working out in Power BI.

I am calling a web API in json format which looks like this

https://wd3-services1.myworkday.com/ccx/service/customreport2/1111/Fct_All_Employees_effective_date?...2019-12-31-08%3A00&format=json

It returns a list of all employees with their team information etc.
To be able to display historic changes of the teams employees belong to, I would like to replace the fixed date with a dynamic parameter. I saw in this post, that it is possible in general: https://community.powerbi.com/t5/Desktop/How-to-pass-a-dynamic-date-parameter-in-the-json-api-in-pow...

Now I am trying to find out if this solution works for my scenario. I would like to connect the date parameter I am putting into the URL to my dimDate table ('dimDate'[Start of Month]) which is my calendar dimension table.

Something like this:

https://wd3-services1.myworkday.com/ccx/service/customreport2/1111/Fct_All_Employees_effective_date?...'dimDate'[Start of Month]-08%3A00&format=json

Is it possible to make such a connection? Could Power BI in this case call more than one report based on different dates at the same time? Could I e.g. create a visual with 'dimDate'[Start of Month] data on the axis, calling a different report for each month and summarizing all of them in one visual? Or is Power BI only able to show one result of the report at a time?

Thanks a lot in advance!

[Anonymous]

Hi @Anonymous,

You can add a custom column with to concatenate root url with datetime field value and use in web connector.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web.Contents("https://wd3-services1.myworkday.com/ccx/service/customreport2/1111/Fct_All_Employees_effective_date?"&Text.From([Column])&"08%3A00&format=json"))

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the quick help @Anonymous - where would I add the column? In a new table or in the data source where I add the web link as source? I already have the column with all dates in another table, could I refer to this one?

[Anonymous · Accepted solution]

Hi @Anonymous,

Yes, you can add a new column to your date table and paste below formula in custom column panel. (notice: replace below the bold part with your date field name)

Web.Contents("https://wd3-services1.myworkday.com/ccx/service/customreport2/1111/Fct_All_Employees_effective_date?"&Text.From([Datefield])&"08%3A00&format=json")

Regards,

Xiaoxin Sheng

[amitchandak]

Refer:

https://community.powerbi.com/t5/Power-Query/Parameters-for-JSON-API-data-Maintain-Query-daily/m-p/1...

https://community.powerbi.com/t5/Desktop/How-to-pass-a-dynamic-date-parameter-in-the-json-api-in-pow...