cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sandipde
Frequent Visitor

How to refresh Dataset on Power BI Services that uses Dynamic Data Sources ?

Hi Friends,

 

I am getting error while refreshing the power bi dataset that uses the dynamic data sources.

 

I googles the issue, found few links like Chris Webb's blog and other too who suggested to use RelativePath or Query parameter, however they have used a simple links. But I am using Web API which contains 3 parameters + 1 subscription key of API. Itried to build the MQuery Code using RelativePath as well as Query parameter but it did not work at all for me. 

We need to pass 3 parameters 1) activitytypeid 2) startdate 3)enddate

activitytypeid I am passing directly but for startdate and enddate I need to calculate dates dynamically and pass to API.

 

Your help really appreciated...!!

 

Below is the code that I am using (I have changed the link and subsciption key as its organizational data) - 

 

let
Source = Json.Document(
Web.Contents(
"https://api.maersk.com/hr/v1/scf-move-termination?activitytypeid=10&startdate="
& #"DynStartDt-60D-BFR-PM-Start-Txt"
& "&enddate="
& #"DynEndDt-BFR-PM-End-Txt",
[Headers = [#"Ocp-Apim-Subscription-Key" = "9f6792b2fadab2d8cca4f3a5abc76847"]]
)
),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded Table1" = Table.ExpandListColumn(#"Converted to Table", "Table1"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table1", {{"Table1", type any}}),
#"Expanded Table2" = Table.ExpandRecordColumn(
#"Changed Type",
"Table1",
{
"user_rq_id",
"usr_display_name",
"title",
"current_store_name",
"manager",
"activity_id",
"activity_datetime",
"updated_datetime",
"activity_type_desc",
"level",
"status",
"notice_type_description",
"new_title",
"new_location"
},
{
"Table1.user_rq_id",
"Table1.usr_display_name",
"Table1.title",
"Table1.current_store_name",
"Table1.manager",
"Table1.activity_id",
"Table1.activity_datetime",
"Table1.updated_datetime",
"Table1.activity_type_desc",
"Table1.level",
"Table1.status",
"Table1.notice_type_description",
"Table1.new_title",
"Table1.new_location"
}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Expanded Table2",
{{"Table1.user_rq_id", Int64.Type}}
),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1", {{"Table1.user_rq_id", "user_rq_id"}}),
#"Changed Type2" = Table.TransformColumnTypes(
#"Renamed Columns",
{{"Table1.usr_display_name", type text}}
),
#"Renamed Columns1" = Table.RenameColumns(
#"Changed Type2",
{{"Table1.usr_display_name", "usr_display_name"}}
),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1", {{"Table1.title", type text}}),
#"Renamed Columns2" = Table.RenameColumns(
#"Changed Type3",
{{"Table1.title", "title"}, {"Table1.current_store_name", "current_store_name"}}
),
#"Changed Type4" = Table.TransformColumnTypes(
#"Renamed Columns2",
{{"current_store_name", type text}}
),
#"Replaced Value" = Table.ReplaceValue(
#"Changed Type4",
null,
"-",
Replacer.ReplaceValue,
{"current_store_name"}
),
#"Changed Type5" = Table.TransformColumnTypes(#"Replaced Value", {{"Table1.manager", type text}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type5", {{"Table1.manager", "manager"}}),
#"Changed Type6" = Table.TransformColumnTypes(
#"Renamed Columns3",
{{"Table1.activity_id", Int64.Type}}
),
#"Renamed Columns4" = Table.RenameColumns(
#"Changed Type6",
{{"Table1.activity_id", "activity_id"}}
),
#"Changed Type7" = Table.TransformColumnTypes(
#"Renamed Columns4",
{{"Table1.activity_datetime", type datetime}}
),
#"Renamed Columns5" = Table.RenameColumns(
#"Changed Type7",
{{"Table1.activity_datetime", "activity_datetime"}}
),
#"Changed Type8" = Table.TransformColumnTypes(
#"Renamed Columns5",
{{"Table1.updated_datetime", type datetime}}
),
#"Renamed Columns6" = Table.RenameColumns(
#"Changed Type8",
{{"Table1.updated_datetime", "updated_datetime"}}
),
#"Changed Type9" = Table.TransformColumnTypes(
#"Renamed Columns6",
{{"Table1.activity_type_desc", type text}}
),
#"Renamed Columns7" = Table.RenameColumns(
#"Changed Type9",
{{"Table1.activity_type_desc", "activity_type_desc"}}
),
#"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns7", {{"Table1.level", type text}}),
#"Renamed Columns8" = Table.RenameColumns(#"Changed Type10", {{"Table1.level", "level"}}),
#"Changed Type11" = Table.TransformColumnTypes(
#"Renamed Columns8",
{{"Table1.status", type text}}
),
#"Renamed Columns9" = Table.RenameColumns(#"Changed Type11", {{"Table1.status", "status"}}),
#"Changed Type12" = Table.TransformColumnTypes(
#"Renamed Columns9",
{{"Table1.notice_type_description", type text}}
),
#"Renamed Columns10" = Table.RenameColumns(
#"Changed Type12",
{{"Table1.notice_type_description", "notice_type_description"}}
),
#"Removed Columns" = Table.RemoveColumns(
#"Renamed Columns10",
{"Table1.new_title", "Table1.new_location"}
),
#"Sorted Rows" = Table.Sort(#"Removed Columns", {{"updated_datetime", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(
#"Sorted Rows",
each (
[title]
= "Sales Consultant" or [title]
= "Sales Consultant " or [title]
= "Sales Consultant-Part Time"
)
),
#"Removed Duplicates1" = Table.Distinct(#"Filtered Rows", {"user_rq_id"}),
#"Extracted Date" = Table.TransformColumns(
#"Removed Duplicates1",
{
{"activity_datetime", DateTime.Date, type date},
{"updated_datetime", DateTime.Date, type date}
}
)
in
#"Extracted Date"

4 REPLIES 4
jennratten
Super User
Super User

Hello - have you try moving everything after the question mark to the relative path?

Hi,

 

Yes I tried below codes but didn't work,

 

Web.Contents(
"https://api.maersk.com/hr",
[
RelativePath = "v1/scf-move-termination",
Query = [
activitytypeid = 2
& startdate = #"DynStartDt-60D-BFR-PM-Start-Txt"
& enddate = #"DynEndDt-BFR-PM-End-Txt"
& Headers = [#"Ocp-Apim-Subscription-Key" = "9f6792b2fadab2d8cca4f3a5abc76847"]
]
]
)

 

****************************AND****************

 

 Web.Contents(
 "https://api.maersk.com/hr",
[
RelativePath="v1/scf-move-termination",
Query=
[
activitytypeid="2",
startdate=#"DynStartDt-60D-BFR-PM-Start-Txt",
enddate=#"DynEndDt-BFR-PM-End-Txt",
]
]
{
Headers = [#"Ocp-Apim-Subscription-Key" = "9f6792b2fadab2d8cca4f3a5abc76847"]
}
)

Hi @Sandipde 

 

startdate=#"DynStartDt-60D-BFR-PM-Start-Txt",
enddate=#"DynEndDt-BFR-PM-End-Txt",

This section may fail the refresh. I guess the parameter values are from two queries in the same report, right? If so, they couldn't return the correct startdate and enddate values before being used.

 

You can open both queries' Advanced editor, add ()=> at the beginning to convert this query into a custom function. Then in the main query, use this function to take place of the query name. Then try refreshing. This may help. 

 

Or you can calculate startdate and enddate dates in the main query directly before the Source step. Then pass them to the Web.Contents() function in Source step. This can ensure the date values are correct and valid before they are used.

 

I hope this would be helpful. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

 

@v-jingzhang,

 

Thanks for your reply and suggestions.

I tried to implement both of yourt suggestions like making them as a function (which is showing error in code) and declaring the startdate and enddate in main code(which works as passing then dynamically, means it's refresing on power BI desktop but not on power bi services.). 

Please let me know if you see any other solutions.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors