Forum Discussion
Transform row values to columns and Table Visual with dynamic number of columns
Greetings all,
I have a data like below, and I wanted to display the Product Category values as column with each brand listed as a comma separated:
| Warehouse | Product Category | Brand |
| W-A | Trousers | Levis |
| W-B | Trousers | Levis |
| WB-C | Dress | Zara |
| WB-A | T-Shirt | Zara |
| WB-A | Shirt | CK |
| WB-B | Vest | Gap |
| WB-C | Vest | Gap |
| WB-C | Sports Wear | Adidas |
| WB-A | Hat | Nike |
| WB-B | Shoes | Nike |
| WA-A | Shoes | Rebook |
| WB-C | Belt | Levis |
| WB-A | Jacket | Zara |
| WB-A | Jacket | Zara |
| WB-B | Jacket | Zara |
| WB-B | Sports Wear | Adidas |
| WB-C | Shoes | Puma |
| WB-A | Shirt | Adidas |
| WB-C | Dress | CK |
| WB-B | Vest | Zara |
| WB-C | Vest | Nike |
| WB-C | Sports Wear | Puma |
I was able to transform the data to the format I wanted using PowerQuery using the M code below:
= Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[#"Product Category"]), "Product Category", "Brand", each Text.Combine(_, ", "))
which resulted:
| Warehouse | Trousers | Dress | T-Shirt | Shirt | Vest | Sports Wear | Hat | Shoes | Belt | Jacket |
| W-A | Levis | |||||||||
| W-B | Levis | |||||||||
| WA-A | Rebook | |||||||||
| WB-A | Zara | CK, Adidas | Nike | Zara | ||||||
| WB-B | Gap, Zara | Adidas | Nike | Zara | ||||||
| WB-C | CK, Zara | Nike, Gap | Adidas, Puma | Puma | Levis |
The issue arises when trying to display data using a Table Visual, as the number of Product Categories is not known at the time of design. For instance, a new entry such as "Suit" or "Scarf" may be added, which should dynamically appear in the Table Visual. Is there a way to use a table visual with a dynamic number of columns?
Hi enoch99 - If you're using Power BI Premium or Power BI Desktop with field parameters enabled, you can dynamically manage fields. Here’s how:
Enable Field Parameters:
Go to Modeling > New Parameter > Fields.
Create a field parameter that includes Product Category.
Use in Visual:
Add the parameter to your visual and select the dynamic fields you want to display. The parameter will update with any new fields.Solved: Field Parameter with Matrix Visual for dynamic col... - Microsoft Fabric Community
Solved: Dynamic column selection for matrix visual - Microsoft Fabric Community
Thanks for the reply from rajendraongole1, please allow me to provide another insight.
Hi enoch99 ,
If you accept to display the data in a matrix visual, you can try the following steps.Not pivoting in Power Query.
Create a measure to be used to display the brand.Brand_ = IF(COUNTROWS('Table')>1,CONCATENATEX('Table','Table'[Brand],","),MAX('Table'[Brand]))
Use the Product Category column to create a field parameter table.Create a matrix visual using the parameter field and the measure.
Now, when you add a new product category, it will automatically appear in the matrix visual.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi enoch99 - If you're using Power BI Premium or Power BI Desktop with field parameters enabled, you can dynamically manage fields. Here’s how:
Enable Field Parameters:
Go to Modeling > New Parameter > Fields.
Create a field parameter that includes Product Category.
Use in Visual:
Add the parameter to your visual and select the dynamic fields you want to display. The parameter will update with any new fields.Solved: Field Parameter with Matrix Visual for dynamic col... - Microsoft Fabric Community
Solved: Dynamic column selection for matrix visual - Microsoft Fabric Community
Thanks for the reply from rajendraongole1, please allow me to provide another insight.
Hi enoch99 ,
If you accept to display the data in a matrix visual, you can try the following steps.Not pivoting in Power Query.
Create a measure to be used to display the brand.Brand_ = IF(COUNTROWS('Table')>1,CONCATENATEX('Table','Table'[Brand],","),MAX('Table'[Brand]))
Use the Product Category column to create a field parameter table.Create a matrix visual using the parameter field and the measure.
Now, when you add a new product category, it will automatically appear in the matrix visual.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you very much rajendraongole1 and Anonymous . Using parameters solved the problem. With this solution, I do not even need to do transformation in PowerQuery. Thanks 🙏
