Add sheet name as a column in appended tables in Power Query

Time to take a big breath and learn about Power Query.

It seems reasonably intuitive, but....

The enclosed file has two tables, on in each of two sheets. I can append them and do all sorts of stuff. However, I want to to be able to import the sheet name as a column into the appended tables.

I don't want to create an extra column, on each raw data sheet, repeating the column name... That's ugly (and easy...). There must be some way of adding the sheet name as a column when appending the tables. I just can't see where.. and my efforts on Google have led me up just so many blind alleys.

So, over to my fellow Excellers...

More daft questions will follow in time...

I’m on the iPad now, Glenn, but I think that if you go with a blank query, make the source =Excel.CurrentWorkbook(), then you will get a sheet name column by default.

This: https://exceloffthegrid.com/power-qu...rent-workbook/

Yes, it’s high time you took PQ on!!!

I'm off for a beer or twain... I did try that... but when I did... sod-all happened...

I'll be back tomorrow...

It relies on there being tables or named ranges, but have another go tomorrow. Enjoy your brew!

I use this technique a fair bit, so yes, it should work!

Excel.CurrentWorkbook only read table name, not sheet name.

Option 1 rename each table name to Bert and Ernie

Please Login or Register  to view this content.

Option 2 use Excel.Workbook to get data from sheet with sheet name. Change the file path in blue.

Please Login or Register  to view this content.