Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Power BI Service " This dataset includes a dynamic data source"

Here is M code that works fine in Power BI Desktop and refresh works fine.



    StartYear = 2021,
    EndYear = 2022,
    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),
    #"Changed Type" = Table.TransformColumnTypes(CreateEndDate,{{"StartDate", type text}, {"EndDate", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "StartDate", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"StartDate.1", "StartDate.2", "StartDate.3"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "EndDate", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"EndDate.1", "EndDate.2", "EndDate.3"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "sdate", each [StartDate.3]&"-"&[StartDate.2]&"-"&[StartDate.1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "edate", each [EndDate.3]&"-"&[EndDate.2]&"-"&[EndDate.1]),
    #"URL" =  Table.AddColumn(#"Added Custom1", "URL", each "<myapi id>&api_key=<my api key>&api_identifier=<my api identifier>&date_from="&[sdate]&"%2012:01:01&date_to="&[edate]&"%2012:01:01&type=CLICKS&include_contacts_data=N&include_extended_data=N&include_split_info=N", type text),
    #"Response" = Table.AddColumn(#"URL", "Custom2", each Web.Contents([URL])),
    #"Invoked Custom Function" = Table.AddColumn(Response, "unzip", each unzip([Custom2])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"unzip"}),
    #"Expanded unzip" = Table.ExpandTableColumn(#"Removed Errors", "unzip", {"FileName", "Content"}, {"unzip.FileName", "unzip.Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded unzip", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([unzip.Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Campaign ID", Int64.Type}, {"Content API Identifier", type any}, {"User ID", Int64.Type}, {"Email Address", type text}, {"Salutation", type text}, {"First Name", type text}, {"Last Name", type text}, {"Sent Date", type datetime}, {"Reference", type any}, {"From Email Address", type text}, {"From Name", type text}, {"Subject Line", type text}, {"Split Type", type text}, {"Open Count", Int64.Type}, {"Opened First", type datetime}, {"Opened Last", type datetime}, {"Link Text", type text}, {"Click Count", Int64.Type}, {"Clicked First", type datetime}, {"Clicked Last", type datetime}, {"Opt-out Count", Int64.Type}, {"First Opted-out", type datetime}, {"Last Opted-out", type datetime}, {"URL", type text}})
    #"Changed Type1"



API link in code above allows us to download only 1 month data.

But in Power BI Service won't refesh with below error 



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:




 Please, advise how to configure Relativepath  for above M code so we can avoid  error in Power BI Service  and have successful refresh. URL  is changed to as it contains sensitive company data

Community Support
Community Support
Super User
Super User

@gingercat123 , I you arrre using a m parameter , then you should be able to set that up in dataset setting -> parameters


Also check this way

Thanks @amitchandak please could help with below line , not sure what i am doing wrong.

= Table.AddColumn(#"Added Custom1", "URL", each "",[Relativepath="rest/agency/campaigns/engagement?api_id=abc&api_key=abc&api_identifier=abc&date_from="&[sdate]&"%2012:01:01&date_to="&[edate]&"%2012:01:01&type=CLICKS&include_contacts_data=N&include_extended_data=N&include_split_info=N"], type text)

i get below error:



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors