Add custom column with sheet name as value content Power Query

[Melimob]

Hi

I am a newbie to Get & Transform/PQ but already love it.

I have a task which I think is quite common and have hunted around the net but can't find what I need.

Simply put
Import all sheets, run some transformation on each one to include add a column with sheet name on each one, then merge.

Therefore I need to run the steps on one sheet and reuse the same query on all? but with dynamic coding.

My workbook
1. My transform steps are:

let    Source = Excel.Workbook(File.Contents("C:\Users\name\Documents\PIT\Templates\Global Matrix Read\Global Capabilities Database TPN Integration Matrix - Masterfile_06APR19.xlsm"), null, true),
    Afghanistan_Sheet = Source{[Item="Afghanistan",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Afghanistan_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"FUNCTION", type text}, {"ITEM", type text}, {"MTN", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"FUNCTION"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ITEM", "Client"}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Renamed Columns"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers1"

2. Now I want to add a column called Country and use the sheet name as the value

3. Then merge as one table for analysis.

The column I removed called 'Function' was like a category which would be nice to keep as it groups all the items for easy filtering but the way the data is structured I couldn't get it to read right any way I tried. Anyhow, this bit is not essential at the mo.

The doc is stored on SharePoint and I tried to link it to that but think my work firewall stops it which I guess means I will have to download a copy to a folder and rerun the query pointing to the new file each time?

Any advice would be greatly received.

Many thanks

 

[theBardd]

Here you would import all files in a folder, that is one of the Get types in PQ. After getting all the fie, the sheet name ill be a column, you just need to expand the tables (the little symbol in the column header) and remove columns you don't want.

 

[Melimob]

Here you would import all files in a folder, that is one of the Get types in PQ. After getting all the fie, the sheet name ill be a column, you just need to expand the tables (the little symbol in the column header) and remove columns you don't want.

Thank you so much theBardd

I used your answer to look up a tutorial and hey presto! it worked and made sense! thank you again!