Add custom column with sheet name

[rlcec]

Hello all,

is it possible to add a custom column with the sheet name as values? 

We have one workbook with different worksheets per country (Portugal, Austria, ...). The Portugal sheet for example looks as follows:

Date	Category	Amount
1-1-2019	Alpha	100
14-4-2019	Beta	250

The aim is to get to this table:

Date	Category	Amount	Country
1-1-2019	Alpha	100	Portugal
14-4-2019	Beta	250	Portugal

Thank you very much in advance for any help on this topic!

[dax · Accepted solution]

Hi rlcec, 

You could try to use below M code to add sheet name

let
    Source = Excel.Workbook(File.Contents("C:\Users\<username>\Desktop\New Microsoft Excel Worksheet (5).xlsx"), null, true),
    country2_Sheet = Source{[Item="country2",Kind="Sheet"]}[Data],
    #"Promoted Headers1" = Table.PromoteHeaders(country2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"country", Int64.Type}, {"anme", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Source{[Item="country2",Kind="Sheet"]}[Name])
in
    #"Added Custom"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

You can merge the tables in Power Query Editor after you have imported the Excel file.

1. import the Excel File in Power Query Editor (each sheet as new table)
2. Create a new query:

( I used Portugal and Spain as table names for reference.)

let
Source = #table({"Tables"}, {{"Portugal"}, {"Spain"}}),
Evaluate = Table.AddColumn(Source, "Custom", each Expression.Evaluate([Tables], #shared)),
Merged = Table.ExpandTableColumn(Evaluate, "Custom", List.Union(List.Transform(Evaluate[Custom], each Table.ColumnNames(_))))
in 
Merged

This should do the trick.