Forum Discussion
PBI table visualization with unique Order IDs from one table and concatenated Items from another tab
Hello, I am transitioning a super simple table visualization from Tableau to PBI and have a lot of challenges. In Tablea it's a few seconds drag and drop and voila - I have the end result, whereas I have not been able to achieve the same result with PBI even after spending a few hours.
I have two tables. Table1 with unique Order IDs and other relevant data and Table 2 with Order IDs from Table 1, addresses and items. One order can have multiple shipment addresses. And each address can have multiple items. It's 1 to many relationship: 1 Order ID from Table1 can have multiple Order IDs in Table2.
Table1
Table2
My desired PBI visualization with unique Order IDs and other columns from Table 1 with concatenated Address, Items and Prices from Table 2 grouped by Address :
How can I achieve this in PBI? Thank you!
You cannot build nested layouts like that with Power BI Desktop (at least not with the default visuals).
Paginated reports can do your layout as-is, https://learn.microsoft.com/en-us/power-bi/paginated-reports/report-design/nested-data-regions-report-builder.
It is not easy as Tableau, agreed!
Similar layout but not same: Out of curiousity, did you try this using matrix ?
1) Assuming you have the relation between header and detail table using order id.2) Drag a matrix layout and add all the fields in the Rows (and not in columns and not in values)
3) Adjust the settings:
4) Expand to the lowest level: in the matrix visual:
Sample output
and adjust the borders per your needs:
Hope it helps!
3 Replies
You cannot build nested layouts like that with Power BI Desktop (at least not with the default visuals).
Paginated reports can do your layout as-is, https://learn.microsoft.com/en-us/power-bi/paginated-reports/report-design/nested-data-regions-report-builder.
It is not easy as Tableau, agreed!
Similar layout but not same: Out of curiousity, did you try this using matrix ?
1) Assuming you have the relation between header and detail table using order id.2) Drag a matrix layout and add all the fields in the Rows (and not in columns and not in values)
3) Adjust the settings:
4) Expand to the lowest level: in the matrix visual:
Sample output
and adjust the borders per your needs:
Hope it helps!Thank you so much for taking the time to respond. I had built a matrix but didn't know you could remove the row header icons "+/-". I have also used CONCATENATEX to merge values but this is much faster. Really appreciate the help!
Glad to hear that it helped. Thank you.
