Scheduled Refresh is not working with function

[Franklin1]

Hello everyone,

Need your help desperately...

I created a report in PBI Desktop.

The data is from Excel file, which is basically 2 columns - Month and URL.

I have created a function (see below) to bring me the number i need from the URL for each month:

(url) =>
let
Source = Web.Page(Web.Contents(url)),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "AAA", "BBB", "CCC", "DDD"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.AAA", "Data.BBB", "Data.CCC", "Data.DDD"}),
#"Data CCC" = #"Expanded Data"{20}[Data.CCC],
CCC= #"Data CCC"{1}[CCC],
CCC1= CCC{2}[CCC],
CCC2= CCC1{2}[CCC],
CCC3= CCC2{0}[CCC],
CCC4= CCC3{1}[CCC][DDD]{0}
in
CCC4

When i try to schedule a refresh in PBI Service i get an error message:

"You can't schedule refresh for this dataset because one or more sources currently don't support refresh"

I know that there is an issue with scheduling a refresh when using a function with dynamic parameter.

What i need is your creative minds of a way to walkaround this issue, because i tried and tried but didn't find anything at this point.

A HUGE KUDOS for the one who could solve my issue.

Thanks a lot!

[tringuyenminh92]

Hi @Franklin1,

So far it's a limitation of PBI for passing parameters in the input of query. You could do workaround by get a list of file in folder by its static link and return list of binary contents. So you could pass the binary content as the input. 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[Franklin1]

Thanks a lot.

I think your solution is valid when we have a number of excel files located in a folder.

The issue is that the my data is not coming from few excel files located in a folder, but it is coming from a web with a dynamic web address.

On a monthly basis, i update the excel file with the web address for the current month.

So taking the list of files in folder is not working.

Do you have any other ideas?

Thanks a lot!

[v-jiascu-msft]

Hi @Franklin1,

Maybe this thread could help. And this blog.

Best Regards!

Dale