Power Query Tips: How to append queries with the table name as a column

This is the start of a blog series providing you with tips for data preparation within the Power Query interface and advice for using M code to achieve this!

Setting the scene:

You have imported your data from an Excel file, and have many sheets for different stores about your employees. These sheets all import as separate queries, however as they have the same data structure and columns, you would want to append them all together to create an employee query.

How to achieve this:

1. CTRL + Select the store location queries

2. From the home tab, select the append queries as new

3. This is the result, however, we now want to edit the M code to be able to bring in the query names so we can understand which stores the employees work at

Edit the M Code:

1. Here we are inserting Table.AddColumn around the query names in which we state the table name, the new column name and then using Each to add the table name for each row of data

You can copy the code here:

= Table.Combine({Table.AddColumn(#"Leeds Store", "source query", each "Leeds")

Ta-dah! That is it, now you have one clean query but still have kept the table name information within the append.