The data I am using is split into 3 sheets (STORE1, STORE2, STORE3 --- not very ideal) and is not formatted in table either.

I'm looking for a way in Power Query to create one column containing all months out of multiple columns containing sales per each month per product (If it isn't clear, screens will probably be helpful)

Basically transforming this : Initial Sheets into this : Final Sheet

The "products" are linked to "type" via another table, and I would also like to have "store number" in the new table format as a header instead of having different sheets

As of now, I haven't found a way to do it.

Thanks already for the help,

CodePudding user response：

Unpivot is the command you ar looking for.

• Make a query for each sheet, the only operation you have to do is to add a custom column (Add column -> custom column, in the formula write just 1 for the query of store 1, 2 for the query of store 2, etc. then rename the new column to "store number"

• append the three queries

• select all the columns except the one from january to december and click on Transform -> drop down menu under unpivot -> unpivot other columns

• I am not sure what you mean when you say that the "products" are linked to "type" via another table but if you mean that you have a look up table where you have a column that matches "products" and another where you have the "type" column, then you can merge your query, I would suggest you make sure you don't have any duplicate in you look up table.