I want to Transpose each sheet and then append all in a single query

[SR_dataguy]

Hi,

I have an excel workbook with many sheets, aroung 30-35 sheets. All sheets have same formatted data and with same column headers. Only 1st column header has sheet name in each sheet. (Example data screenshot below)

In Power BI Power query, I'll import folder and load file over in power query. Layter I want to transpose each sheet individually and then append one below other with sheet name as 1 column so that we can filter data later. (below image is to show how it should transpose)

We can do each query for each sheet and then append but I want all this to be done automatically if any other sheet added/deleted/modified. So I want it to be done all in single query.

How to do this? Please help me here. 

Many thanks in advance. Cheers.

[Anonymous · Accepted solution]

So if you have the query named "Example" that correctly transforms one sheet, add this before the first "let"

(ColumnName as table) =>

This will turn your query into a function. Then go to your list of tables (the Excel sheets) and under add columns, choose "Invoke Custom Function". Use "Example" query as your function, and Content (it could be Data) as your parameter.

--Nate

[SR_dataguy]

Great man, thanks a ton. It worked. You saved a lots of time.