Forum Discussion
Roll Up Child Rows to new Parent Row
I'm working on some spreadsheets that I'm trying to integrate from SmartSheet. PowerBI doesn't seem to support collapsing rows as part of a parent-child hierarchy, so I'm looking for a work around.
I've tried importing the SmartSheet without the parents collapse rows, and now implementing a query to roll up the corresponding child rows into a new parent aggregation row, to simulate the collapsing row hierarchy. From what I can read, roll ups cannot create a new row in the data?
Is there any way to roll up these child rows into a new parent row, to simulate collapsing hierarchy?
3 Replies
Does anyone know if this is even possible?
PhilTerz wrote:
I'm working on some spreadsheets that I'm trying to integrate from SmartSheet. PowerBI doesn't seem to support collapsing rows as part of a parent-child hierarchy, so I'm looking for a work around.
I've tried importing the SmartSheet without the parents collapse rows, and now implementing a query to roll up the corresponding child rows into a new parent aggregation row, to simulate the collapsing row hierarchy. From what I can read, roll ups cannot create a new row in the data?
Is there any way to roll up these child rows into a new parent row, to simulate collapsing hierarchy?
Could you post any sample data and expected output? As to parent and children, maybe you're refering to a Many to one relationship?
This is an example of the data in SmartSheet, where we have the +/- system of collapsing rows, and an example of that aggregation in the Duration column (not all data is shown, but you can assume each parent collapsing row aggregates the duration of all the child ones underneath it).
Importing this into PowerBI though, all those relationships are lost. Autodetect doesn't detect any of them, so I was trying to find a way to build this relationship up in the Data Model so I can use the visualization charts and tables correctly.
Many-to-one seems to be between multiple tables, if I understand correctly?
