Power Query - Dynamic selection of document name

[ivanuska]

Hello,

I would need your help with dynamic selection of excel file on OneDrive. We have script that is creating excel file (.xlsm) and saving it into OneDrive in format "Document Name - DD/MM/YYYY". Based on either timestamp in name or document creation date, I am able to create ranking of documents and define the newest file.

The problem appears, when I want to unpack the file by selecting Binary on Content column. Once selected, Power Query automatically creates step with hard-coded document name. Once the new file is generated, the query will fail on this step as the document name has changed. Please see below screenshots.

Is there any way how to overcome this issue by dynamic selection of document name? I am able to create query that will define the newest file - I just need to copy the name of the document into dynamic variable and then replace the hard-coded text by this variable.

Sorry but I have very limited knowledge to write own functions in M language so I would need step-by-step help (ideally with screenshots).

Thank you for any help!

Regards

Ivanuska

[OwenAuger · Accepted solution]

Hi @ivanuska 

The easiest way to fix this is to edit the code in the formula bar of your 2nd screenshot by replacing the code between the curly braces with "0"

In other words, change the code for this step to:

= #"Filtered Rows1"{0}[Content]

The zero corresponds to the first row of the table from the previous step named "Filtered Rows1" (using a zero-based index). Since you have already filtered the table to a single row, the first row will always correspond to the file you want to use.

Here is a short recording of me starting with a query similar to yours and editing the M code for that step:

Does this work for you?

[ivanuska]

Thank you very much for super-fast and super-helpful response!