Refreshing excel data which needs to be unpivoted

[thorben]

Hi,

i downloaded weekly sales data for 2020. Now i need to refresh the sales data every week with the new week's sales data. The sales data comes in this form:

category	brand	product	week1	week2	week3
steel	brand1	product1	4€	5€	9€
wood	brand2	product2	4€	5€	9€

I need to unpivot the week sales columns in power bi to have one sales (€) column.

However, if i now want to add the new week sales, i do not know how to. I cannot manually copy paste the new week sales data to the master excel, since some products the rows are not in the same order. Some products might have been added so the rows are not identical. 

New Weeks sales excel might look like this, with brand3 being a newly launched product

category	brand	product	week4
steel	brand1	product1	7€
steel	brand3	product3	1€
wood	brand2	product2	8€

Can someone help me to add new week sales to the excel or solve this in power bi? I was researching the merge & append functions, but i think this does not help here. 

Thank you very much!

Thorben

[v-rzhou-msft · Accepted solution]

Hi @thorben 

You can try unpivot columns and append function in Power Query Editor.

Import your tables in excel into power bi desktop.

Week Sales Table:

Week4 Table:

Select product column and week columns per table and unpivote them.

Then append two tables.

Finally, pivote Attribute and Value columns(choose don't aggregate) Result is as below.

You can download the pbix file from this link: Refreshing excel data which needs to be unpivoted

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[thorben]

Thanks, that worked for me!

[mahoneypat]

Not clear if you are combining multiple files or a single file that contains data for all weeks.  In either case though, the steps are the same.  Assuming you are combining multiple files (the more complex case), use the Combine & Transform feature so that you have an example file transform (and its related function).  In that query, select the first three columns, right click and choose Unpivot Other Columns.  This should work on both example files you showed above, and the results will be appended.

Regards,

Pat

[thorben]

Thanks for your reply, Pat!

I am not sure if i should combine multiple files or a single file. I need to update the sales data on a weekly basis, therefore i need to save a new excel as a query (sharepoint folder for example) or append the new weeks in a master excel, which i am unable to to because of the structure of the raw data. I tried to duplicate the first query and change the source to the new excel file. However, i receive this error

Some columns are not the same for both queries. The first query has some weeks sales (as columns) that are not included in the new one. The new one has some new week sales added. Can you help with this? 

If the copying of applied steps to the new query works, i need to append or merge the queries?