Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I'm struggling with the following.
I need to fetch data from a file that is updated weekly, if it would keep the same name it would not be an issue, but....
The file changes name weekly;
week 4 in 2024 it is named: ISP RFS Cumulative_EMEA Raw Data 2024-04.xlsx
week 5 in 2024 it is named: ISP RFS Cumulative_EMEA Raw Data 2024-05.xlsx
The first characters of the file are always the same: "ISP RFS Cumulative_EMEA Raw Data".
there is always only 1 file with the string: "ISP RFS Cumulative_EMEA Raw Data" (in this example the file ISP RFS Cumulative_EMEA Raw Data 2024-04.xlsx will be deleted when ISP RFS Cumulative_EMEA Raw Data 2024-05.xlsx is uploaded.
So I need a PowerQuery fetching me the data from the file that starts with "ISP RFS Cumulative_EMEA Raw Data" to keep my Reports and Dashboard accurate andactual.
At this moment I change my Query frequently but it should be possible to do that smarter. This what I have:
======================================
let
Source = SharePoint.Contents("https://xxxxxxxxxxxxxxxxxx/", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"99 Data Files for PBI" = #"Shared Documents"{[Name="99. Data Files for PBI"]}[Content],
#"Data" = #"99 Data Files for PBI"{[Name="Data"]}[Content],
#"Filtered Rows" = Table.SelectRows(#"Data", each Text.StartsWith([Name], "ISP RFS Cumulative_EMEA Raw Data")),
#"ISP RFS Cumulative_EMEA Raw Data 2024-04 xlsx" = #"Filtered Rows"{[Name="ISP RFS Cumulative_EMEA Raw Data 2024-04.xlsx"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"ISP RFS Cumulative_EMEA Raw Data 2024-04 xlsx"),
#"RFS Billing_Sheet" = #"Imported Excel Workbook"{[Item="RFS Billing",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"RFS Billing_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx})
in
#"Changed Type"
======================================
Would you have an idea how to resolve this in a smarter way,
Looking forward to your response,
Brgds Kees
Solved! Go to Solution.
Hi @Kees
I believe you can combine files from sharepoint location as well. Sort by created date and index it and keept the lowest index:
How to combine files from sharepoint:
https://www.youtube.com/watch?v=mYkNDdjbFvo
Another approach we may think of is using a parameter storing file name and using in your query. everytime that parameter is updated, your main query will work dynamically. I can not test your code for this approach, becuase of dummysharepoint address, but I think above shared link might help you.
Hope it helps.
Thanks for your quick response, this solution however is not what 'm looking for for multiple reasons, one of the the file I need is on a sharepoint location, not is a folder used locally.
Trying to find a solution in the direction of addjusting the code in the first message to petch the file...
Again thanks for your suggestion!
Brgds Kees
Hi @Kees
I believe you can combine files from sharepoint location as well. Sort by created date and index it and keept the lowest index:
How to combine files from sharepoint:
https://www.youtube.com/watch?v=mYkNDdjbFvo
Another approach we may think of is using a parameter storing file name and using in your query. everytime that parameter is updated, your main query will work dynamically. I can not test your code for this approach, becuase of dummysharepoint address, but I think above shared link might help you.
Hope it helps.
Hi @Kees
I believe you can use folder option instead of a single file. That would then be just combing all the files in that folder, since you eventually have one file everytime, so infact result will be from a single file.
Addtionally if suits your case, with this option, you can duplicate the file name column and split it with common part and incremental part and filter the incremental part with descenting order, add index column, and keep that smallest index. This will ensure, even if older files remain in parallel somehow, query will not use those.
Hope it helps.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |