Here is M code that works fine in Power BI Desktop and refresh works fine.
let
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 "https://abc.com/rest//engagement?api_id=<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}})
in
#"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: https://aka.ms/dynamic-data-sources.
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
