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 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. with OneDrive) + 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"]
    )
    ...

Hi Idreeesi 

Doable in principle. However, this raises some first questions:
1 - Where will you get your PDF from (OneDrive business, SharePoint, Folder on a local HD/Server...)?
2 - Will you and your colleagues use the same Folder path to the PDFs?

In principle, this requires an additional query that returns a list of the PDFs available in a given Folder. Then the user would select a file name in that list. This could look like this with 2 parameters on an Excel sheet:

This would be reduced to 1 parameter if you and your colleagues use the same Folder path in which case you could hard-code it in the additional query

Lorenzo 

Thank you again. This worked like a charm! I'm sure my colleagues will be as impressed as I am 🙂

One last (cosmetic!) step at the 'Source' step (at the very start of the query). What is the easiest way for allowing the users to choose which PDF (i.e. weekly report) to choose? Can we do this through a 'Parameter' or any other method? 

Idreeesi 

Glad I could help. A less complex approach starting with your Source table:

where I need the value of the top-left cell ("Progress for week 02") to repeat on every row

    Source = ...,
    WeekNum = "week " & Text.AfterDelimiter(
        Table.FirstValue(Source), // Top-left cell of the Source table
        "week"
    ),
    RemovedTopRow = Table.Skip(Source),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRow, [PromoteAllScalars=true]),
    AddedWeekNo = Table.AddColumn(PromotedHeaders, "Week No", each WeekNum),
    ReorderedColumns = Table.SelectColumns(AddedWeekNo,
        {"Week No"} & List.RemoveItems(Table.ColumnNames(AddedWeekNo), {"Week No"})
    )

Corresponding sample attached