This dataset includes a dynamic data source: how to replace "each <function-call>"?

[Boeboey]

Hi,

I got my query working on the desktop version, but not in the app:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

I've read several related topics but couldn't figure out how to solve my issue.

This is the query I have:

let
    Source = #table({"AssetName", "DeviceID"}, {
		{"1207","110934"},
		{"1295","107027"},
		{"1554","107024"},
		{"1580","107025"},
		{"1580","119971"}
	}),

    #"Added Custom" = Table.AddColumn(Source, "AssetDevice", each [AssetName]&" ("&[DeviceID]&")"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"AssetName", Int64.Type}, {"DeviceID", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetSensorEvents", each GetSensorEvents([DeviceID])),
    #"Expanded GetSensorEvents" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetSensorEvents", {"Midnight", " Direction", "deviceId", "eventDateTime", "FullDate", "Year", "Week of Year", "Weeknr", "Operational", "Index", "Time"}, {"Midnight", " Direction", "deviceId.1", "eventDateTime", "FullDate", "Year", "Week of Year", "Weeknr", "Operational", "Index", "Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded GetSensorEvents",{{"Index", type number}, {"FullDate", type date}, {"Operational", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"deviceId.1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Midnight", type datetime}, {"eventDateTime", type datetime}, {"Time", type time}, {"Week of Year", type number}, {"Year", type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Week of Year", "Weeknr"})

in
    #"Removed Columns1"

So I'm calling function GetSensorEvents:

(DeviceID as number) =>
let
    <first some initialization>
    url = "https://api.mywebsite.com/api/device/"&Number.ToText(DeviceID)&"/data/events/export",
    GetJsonQuery = Csv.Document(Web.Contents(url, [Headers=[Authorization=AccessTokenHeader, Accept="application/json"]]),[Delimiter=";", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(GetJsonQuery,{"Column2"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Date", "Midnight"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Midnight", type datetime}, {" Direction", type text}}),
    #"Added eventDateTime" = Table.AddColumn(#"Changed Type", "eventDateTime", each DateTime.AddZone(DateTime.From([Midnight]),0)),
    #"Added Custom" = Table.AddColumn(#"Added eventDateTime", "deviceId", each DeviceID),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Midnight", "FullDate"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"FullDate", type date}}),
    #"Appended Query" = Table.Combine({#"Changed Type1", Date}),
    #"Replaced Value0" = Table.ReplaceValue(#"Appended Query",null,each [Midnight],Replacer.ReplaceValue,{"eventDateTime"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value0",null,DeviceID,Replacer.ReplaceValue,{"deviceId"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{" Direction", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Operational", each [#" Direction"]*[#" Direction"]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Operational", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3",null,0,Replacer.ReplaceValue,{"Operational"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"Month", "Month Name", "Quarter", "Week of Month", "Day", "Day of Week", "Day of Year", "Day Name"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Midnight", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Inserted Time" = Table.AddColumn(#"Added Index", "Time", each DateTime.Time([Midnight]), type time)
in
    #"Inserted Time"

Any idea how I can rewrite my function call:

Table.AddColumn(#"Changed Type", "GetSensorEvents", each GetSensorEvents([DeviceID]))

I tried the following:

Table.AddColumn(#"Changed Type", "GetSensorEvents", Table.Combine(GetSensorEvents(110934),GetSensorEvents(107027),GetSensorEvents(107024),GetSensorEvents(107025),GetSensorEvents(119971)))

But this didn't work.

Any help?

[Boeboey]

Thanks for your support Team _ Jing!

Your solution worked!

I managed to get rid of the "dynamic data source" error.

Unfortunately, I get another error now: [Unable to combine data] Section1/Assets/Changed Type5 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

But I'm trying to fix this as well.

[v-jingzhang · Accepted solution]

Hi @Boeboey 

To refresh the dynamic data source in Power BI Service, you can use the RelativePath and Query options with the Web.Contents M function. You could first refer to the following blogs to get some basic understanding. 

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

Setting a scheduled refresh on a Dynamic Data Source in Power BI 

According to your URL, you can try using RelativePath option. Your URL structure is similar to the example in above latter link.

From

url = "https://api.mywebsite.com/api/device/"&Number.ToText(DeviceID)&"/data/events/export", 

GetJsonQuery = Csv.Document(Web.Contents(url, [Headers=[Authorization=AccessTokenHeader, Accept="application/json"]]),[Delimiter=";", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),

to

baseURL = "https://api.mywebsite.com/api", 

GetJsonQuery = Csv.Document(Web.Contents(baseURL, [Headers=[Authorization=AccessTokenHeader, Accept="application/json"], RelativePath="device/"&Number.ToText(DeviceID)&"/data/events/export"]),[Delimiter=";", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]), 

You may need to adjust the baseURL because the trick of using the RelativePath and Query options with Web.Contents() only works if the first parameter (baseURL) passed to Web.Contents() is a url that itself can be called on its own without an error.

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