• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

[PowerQuery - Append] include column with sheet name

rolo

Member
Hello, when I consolidate many sheets using append (1 sheet = 1 table)

1. In the final consolidated table: is there any way to have a new column that shows sheet name?

2. Or new column that shows some cell value?

For example: consolidate 4 sheets > North, West, South, East
So in final consolidated table, I will need a new "Region" column that can show sheet name for each record

I will appreciate your help, thanks!
 
Is consolidation done for tables on same workbook? Or is consolidation done on separate workbook from table source?

If former it's fairly simple. But transformation should be applied at initial stage.

Load blank Query. Go to advanced editor and set Source to "Excel.CurrentWorkbook()".

You will see name listed in column. Filter out any unneeded content by name.
upload_2016-9-27_14-58-48.png

Then expand content.

All columns on the table will be expanded with "Content" prefix and you will have "Name" column (usually table names).

upload_2016-9-27_14-59-18.png

Apply additional transformation as needed.

Edit: Added screenshots.
 
Last edited:
Thanks Chihiro!

I just need worksheet name (not table name) in a new column

If Table 1 is located in sheet called "West", then when I consolidate it, I need a new "Region" column that can show the text "West" for each row of Table 1

If Table 2 is located in sheet called "South", then when I consolidate it, I need that "Region" column show the text "South" for each row of Table 2

Does your solution apply for this or do I need some M formula?

Thanks!
 
Sheet names are more pain to extract in PowerQuery. You will need to write custom M function to pull info.

If possible, rename tables to desired name and use that.

Or if tables are fixed (i.e. same tables consolidated) all the time. Then just add custom column with fixed text at each table transformation stage.

Another method is to use CELL function in the table itself to pull sheet name.
 
Ok, below is the easiest way to set up workbook and parameter to retrieve sheet name in consolidated table.

1. Add sheet for storing named range. I named it "NamedRange".

2. Anywhere on the new sheet, enter following formula (replace NamedRange with your sheet name).
=SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]NamedRange","")
This is used to dynamically change file path and file name and pass to PowerQuery.

3. Add the cell containing above formula to named range. I named it "lstFile".

4. Open blank query and modify M in Advanced Editor as below
Code:
let
    path = Excel.CurrentWorkbook(){[Name="lstFile"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(path), true)
in
    Source

5. This will load following table in PowerQuery
upload_2016-9-28_10-28-27.png

6. Set Text filters "Does Not Equal..." "Master" AND "NamedRange"

7. Expand "Data" column (Uncheck "Use original column name as prefix")

8. Perform any additional data transformation needed.

This method and the first method in previous post does not require any additional append procedure as all sheets/tables are brought in together as is (so long as columns match between sheets/tables).

See attached file as well.
 

Attachments

  • Sample_WSName.xlsb
    22.6 KB · Views: 93
Back
Top