Power Query fetching data from file which changes weekly

[Kees]

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

[Kees]

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

[mahenkj2 · Accepted 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.

[mahenkj2]

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.

[mahenkj2]