How to extact the product name from Excel worksheet tab?

[OZMckenzie]

Hello,

I have a Excel workbook of  inspection data for multiple products. I'd like to do "Append" in Power Query to combine these sheets. However, the product name is not included in the datasheet, but on the tab. Please see the attached pic:

I'd like to add the product name in each worksheet, so I can identify the product name after appending these sheets. Please advise how to capture the name on the tab and add into sheets?

Thanks in advance!

[AlB]

Hi @OZMckenzie 

Try this:

let
    Source = Excel.Workbook(File.Contents(Your_File_Path), null, true),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.AddColumn([Data], "SheetName", (outer)=> [Name]))
in
    #"Added Custom"

with Your_File_Path a parameter containing the path to your file. The first step will yield something like this:

Then the second step adds a new column to the tables in [Data] (contents of each sheet or table) with the name of the Sheet. In the column Kind you can see the type of each item.

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[OZMckenzie]

Hello @AlB ,

Thank you very much for advice!

I'm new to Power Query. Here is what I tried:

Loading one sheet from the workbook.

Click the "source" in the applied steps to get the same view as your screenshot.

Do you mean to add a custom column with the formula?

I found another way to populate the product name in datasheets, but not sure if this is the correct way:

Removing other columns, leave only product name and data, and expand the data. All sheets in this workbook are then stacked and also have the product names in the first column.