Restructure table: create row entries based on column value

[pudel]

I'm trying to create a revenue reporting for multiple stores.

The source data is queried from a web-api for each store seperately and structured like:

Date	Invoice#	Customer	PayMethod	Total	serviceCat1	serviceCat2	prodCat1	prodCat2
01.01.2022	1	A	cash	1000	200		800
01.01.2022	2	B	cc	25				25
02.01.2022	3	C	paypall	333.5			233.5	100
02.01.2022	4	D	cash	666		666

How can I bring that into a form that I have the product/service categories in a "category" and the corresponding value in a "amount" column -> creating additional rows if multiple categories are used for one original entry like:

Date	Invoice#	Customer	PayMethod	Category	Amount
01.01.2022	1	A	cash	serviceCat1	200
01.01.2022	1	A	cash	prodCat1	800
01.01.2022	2	B	cc	prodCat2	25
02.01.2022	3	C	paypall	prodCat1	233.5
02.01.2022	3	C	paypall	prodCat2	100
02.01.2022	4	D	cash	serviceCat2	666

...so I can create a report that is filterable by "category".

Some shops have 20+ "categories", so filtering each column by !=NULL and then appending the 20+ resolving seperate tables isn't really an option given the >10 shops...

Any help/hints is highly apprechiated!

[v-jingzhang]

Hi @pudel 

Have you tried @HotChilli 's method? That should work. Feel free to let us know if you have any questions. 

Here are documents for your reference.

Unpivot columns | Microsoft Docs

Pivot and Unpivot with Power BI - RADACAD

Best Regards,
Community Support Team _ Jing

[HotChilli · Accepted solution]

Select the columns from Date to Total.  Right-click the header and choose Unpivot Other Columns.

I think that'll work.  Let me know how you get on.