Forum Discussion
Add custom column with sheet name
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!
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 MergedThis should do the trick.
