Forum Discussion
Restructure table: create row entries based on column value
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!
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.
2 Replies
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.
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
