Dynamic data source - no refresh

[wdvro]

Hello,

ello,

I was experiencing the same kind of issue as the original poster of this post (https://community.fabric.microsoft.com/t5/Desktop/Iterating-over-start-and-end-dates-for-multiple-AP...). 

I applied the suggested M code to my case in Power BI Desktop with positive result, all available rows were imported via the API.

However, I encounterd another problem. I saved the report on the cloud service and tried to enable autorefresh. But this isn't possible, following error is displayed:

Something went wrong

This data set contains a dynamic data source. Because dynamic data sources are not refreshed in the Power BI service, this data set is not refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Please try again later or contact support. Please provide the following information when contacting support.

[wdvro]

Thank for the reply.

Below you find the code I'm using.

Keep in mind that I'm a Power BI novice, so I suppose a professional would written this better.

let
StartYear = 2022,
EndYear = 2025,
Years = Table.FromList({StartYear..EndYear}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
CreateStartDate = Table.AddColumn(ShowMonths, "StartDate", each #date([Column1],[Month],1), type date),
CreateEndDate = Table.AddColumn(CreateStartDate, "EndDate", each Date.EndOfMonth([StartDate]), type date),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(CreateEndDate, {{"StartDate", type text}}, "nl-BE"), "StartDate", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"StartDate.1", "StartDate.2", "StartDate.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"StartDate.1", Int64.Type}, {"StartDate.2", Int64.Type}, {"StartDate.3", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartDate Day 2dig", each Number.ToText([StartDate.1],"D2")),
Custom1 = Table.AddColumn(#"Added Custom", "StartDate Month 2dig", each Number.ToText([StartDate.2],"D2")),
#"Added Custom1" = Table.AddColumn(Custom1, "StartDate Zero", each "000000"),
#"Inserted Merged Column" = Table.AddColumn(#"Added Custom1", "StartDate", each Text.Combine({Text.From([StartDate.3], "nl-BE"), [StartDate Month 2dig], [StartDate Day 2dig], [StartDate Zero]}, ""), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"StartDate.1", "StartDate.2", "StartDate.3", "StartDate Day 2dig", "StartDate Month 2dig", "StartDate Zero"}),
#"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"EndDate", type text}}, "nl-BE"), "EndDate", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"EndDate.1", "EndDate.2", "EndDate.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"EndDate.1", Int64.Type}, {"EndDate.2", Int64.Type}, {"EndDate.3", Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Column1", "Month", "StartDate", "EndDate.1", "EndDate.2", "EndDate.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"EndDate.1", "EndDate.1 Day 2dig"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "EndDate.2 Month 2 dig", each Number.ToText([EndDate.2],"D2")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Enddate Zero", each "000000"),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Enddate Zero", type text}, {"EndDate.2 Month 2 dig", type text}, {"EndDate.3", type text}, {"EndDate.1 Day 2dig", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"EndDate.3", "EndDate.2 Month 2 dig", "EndDate.1 Day 2dig", "Enddate Zero"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"EndDate"),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"EndDate.2"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns1", "Base URL", each "https://api.hellocustomer.com/V1.0/EN/campaign/**campaigncode**/answers/getall"),
#"Merged Columns1" = Table.CombineColumns(#"Added Custom4",{"Base URL", "StartDate", "EndDate"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"URL"),
#"Added Custom5" = Table.AddColumn(#"Merged Columns1", "Custom", each Json.Document(Web.Contents([URL], [Headers=[Authorization="basic **token**"]])))
in
#"Added Custom5"

[Brunner_BI]

It would help if you posted your M code here.

Generally, you cannot reference other queries or parameter in your query, then it will not refresh in the Service since it is a dynamic data source.

Meaning everything has to be in one query.