Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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.
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.
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.