Forum Discussion

Franklin1's avatar
Franklin1
Advocate I
8 years ago

Scheduled Refresh is not working with function

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!

Need Help

3 Replies

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    8 years ago

    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's avatar
      Franklin1
      Advocate I
      8 years ago

      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!