Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jf90
Frequent Visitor

Dynamic data source will not refresh in PBI service

Our organization has a sharepoint file that updated weekly with a new file suffix (File_MMDDYYY). So, for example, the online excel workbook is saved as "File_02062023.xlsx " at the start of one week, and updated as "File_02132023.xlsx ". I wrote the query "dynamicDate" to update the file's suffix weekly so that it updates to the correct date, and this works great in desktop but not in PBI service. Below is a sample of the beginning of the code. 

 

 

let
Source = Excel.Workbook(Web.Contents("https://xyz.sharepoint.com/sites/file/" & dynamicDate), null, true)
in
Source

 

 

Is there a way to alter this code so that I can refresh the dynamic data source in PBI Service?

4 REPLIES 4
jf90
Frequent Visitor

Hi @GilbertQ,

 

Unfortunately the team that updates this data has their own purpose for this file, so our team doesn't have much control over the data management. We are trying to find a solution to suit our secondary use of the data. There are definitley a few manual workarounds, but I'm hoping to automate the process for ease of use. The query method I've been using (& dynamicDate) works great locally, I'm just not sure why it won't work in PBI service as well. 

Hi @jf90 

 

Are you able to copy the file?

 

If you are you could then copy it to another SharePoint location and part of the copying process is to then rename it?

 

As to why it does not work, before a refresh starts it checks to make sure that the file is valid, and when the filename is dynamic it cannot check to make sure it exists and that is why it is not allowed currently.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

jf90
Frequent Visitor

Hi @GilbertQ,

 

I think that is probably how I will need to go about it, although it is a bit more manual of a process than I was hoping. Thanks very much for the explanation!

 

Best,

Jon

GilbertQ
Super User
Super User

Hi @jf90 

 

What I would do is to find a way to change the weekly excel filename to be consistent.


For example I would have a process each week which would copy the latest file and rename it to file_CurrentWeek.xlxs


Then you could just use this name in your query?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors