Table GroupBy using multiple parent levels

[Anonymous]

Hello,

I have a dataset similar to the one below for project management (with multiple parents).

ID	Tittle	Level	Parent_Tittle	Subproject
1	Project Tittle 1	1	Project Tittle 1	SUB1
2	Project Tittle 1	2	SUB1	ENDPROJECT1
3	Project Tittle 1	1	Project Tittle 1	SUB2
4	Project Tittle 1	2	SUB2	ENDPROJECT2
5	Project Tittle 1	1	Project Tittle 1	SUB3
6	Project Tittle 1	2	SUB3	ENDPROJECT3
7	Project Tittle 1	2	SUB3	ENDPROJECT4
8	Project Tittle 1	3	ENDPROJECT4	ENDENDPROJECT1
9	Project Tittle 1	3	ENDPROJECT4	ENDENDPROJECT2
13	Project Tittle 2	1	Project Tittle 2	SUB4
15	Project Tittle 2	2	SUB4	ENDPROJECT5
16	Project Tittle 2	2	SUB4	ENDPROJECT6
17	Project Tittle 2	2	SUB4	ENDPROJECT7
18	Project Tittle 2	3	ENDPROJECT7	ENDENDPROJECT3
19	Project Tittle 2	2	SUB4	ENDPROJECT8

I would like to create a table for visualization that is collapsible and respects multiple parent criteria, similar to the mockup below.

I tried to do it using a matrix, but I was unsuccessful. I also searched here on the forums, but each case was very specific to the user's dataset.

Could anyone help on:

• Creating (or formatting) the table and visual?
• Creating a column that indicates if the row has any child (eg. if it is not a parent from any other following row)? I need to filter the last items for each ProjectTitle

Thank you in advance.

@Edit: reformat table, misspellings and better clarifitcation.

[Anonymous]

Hi @Anonymous ,

Try rebuilding the table by level like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZGxDsIgEEB/pbmZhYOWdlW7OGij7USYTAeNSRPD/8fIaQx4NMERuJcHD2tBgoDhsdzmi6/Gq/f3uXptZbbP00aCExaQP8fPjID+sBtOx32/HYlQpSIMmF4VYSQioi4VqYA1qyIViYgwBYQORMsT6WhYpf26v1hKIpn4yDd5357uK5mW+H2ijtw1IUzHLNIQwoTMIoYQpiT+1DBpDfo3yaTM+lpw7gk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Tittle = _t, Level = _t, Parent_Tittle = _t, Subproject = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Tittle", type text}, {"Level", Int64.Type}, {"Parent_Tittle", type text}, {"Subproject", type text}}),
    #"Filtered Rows" = 
    let
        level1 = Table.SelectRows(#"Changed Type", each ([Level] = 1)),
        level2 = Table.SelectRows(#"Changed Type", each ([Level] = 2)),
        level3 = Table.SelectRows(#"Changed Type", each ([Level] = 3)),
        table12 = Table.ExpandTableColumn(Table.NestedJoin(level1, {"Subproject"}, level2, {"Parent_Tittle"}, "Data", JoinKind.LeftOuter), "Data", {"Subproject"}, {"Subproject.1"}),
        table123 = Table.ExpandTableColumn(Table.NestedJoin(table12, {"Subproject.1"}, level3, {"Parent_Tittle"}, "Data", JoinKind.LeftOuter), "Data", {"Subproject"}, {"Subproject.2"})

    in
        table123
in
    #"Filtered Rows"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[PijushRoy]

Hi @Anonymous 

Please break the table as per level and create a relationship. Please find the attached PBIX below

Update matrix layout as per your requirement

Let me know if that works for you

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Anonymous]

Hello,

Firstly, thank you for your help and time invested.

It kinda works: problem is that I have a dataset around 800K rows, and sometimes I can have "ENDPROJECT4 > ENDENDPROJECT2" or a new "ENDPROJECT4 > ENDENDPROJECT77 (new)" attached to Multiple Project Tittles (first column). At least in my matrix, every ENDENDPROJECT is being shown when I expand the Project Tittle 1 (also the Project Tittles 2, 3 and so on than have the same project name". In other words, it should properly filter every column expanded before.

[PijushRoy]

Hi @Anonymous 
Understood
It depends on how many level do you have in level column (3rd in your demo data)