Custom Column with a particular cell's value (Power Query)

Lorenzo 

Thank you so much. This now perfectly fine 🙂

I just didn't know that the line that starts with 'Source' (highlighted below) is also part of this solution (and thought that the suggested steps end with "// Go:"

Nevertheless, I can't thank you enough for your support. I am left with minor enhancements that I will need to make some research on. I'm glad that the main one has been accomplished:)

Idreeesi 

The code in your last picture shows you hard-coded the folder path and the file name (Weekly Progress Week 02.pdf) in fonction https://docs.microsoft.com/en-us/powerquery-m/file-contents, hence the result you get...

Look again at the file I shared and replicate:

- Create a Parameter and name it FolderPath with Current Value: Z:\WI\WI Financials\Finance\AR\Invoicing - Power Query\Weekly Invoicing\

- Edit your main query code and use exactly the code I posted in my previous reply

If still not good please upload and share your file (i.e. https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07) + post the link here or attach it to your next reply if you're allowed to do it

Lorenzo 

Thank you for your help. I manage to make the drop down list. However, I couldn't run it in the query. The screenshot below shows that I have chosen "Week 1" from the drop down list, but the table still shows Week 2 (I think the issue is with the 'FirstValue' function at the start of the query).

This shows that the weeks are available (are are being updated on refresh). Note: this means that I can't hide the sheet as suggested, as the team need to refresh this sheet to be able to see the new week's report (PDF file).

Lastly, I'm sharing the steps I have followed (as per your advice) in he Advanced Editor: 

Please let me know where I went wrong, and many thanks for your continued support.

Idreeesi 

In the attached file you'll find 2 queries:

- FolderPath: update it with the path the folder where your PDFs are stored. Don't forget the \ at the end

- PdfInFolder: this query auto-refresh when the Excel file opens (so it'll faill when you open it 1st time as it currently refers to my local folder). The query loads to a sheet (can be hidden later)

In the Name Manager:

- FileList: refers to the PdfInFolder table. Used to populate the drop-down list in Sheet1!C2

- SelectedFileName: refers to Sheet1!C2 and you'll use it at the beginning of your existing as follow:

let
    // Get File name from Excel named range "SelectedFileName":
    FileName = Table.FirstValue(
        Excel.CurrentWorkbook(){[Name="SelectedFileName"]}[Content]
    ),
    // Concat. Folder path, file name and pdf extension:
    FullFileName = FolderPath & FileName & ".pdf",
    // Go:
    Source = Pdf.Tables(File.Contents(FullFileName),
        [Implementation="1.3"]
    )
    ...