Need Help to combine

[athul00000]

Hello Team, 

Can anyone please help me with the below table, I have loaded the below table on the queries but im unable to categorize them correctly. 

The table contains the topics for each Brand. Each "name" has an "id", a "parentId" and its "level" in the Heirarchy. 

parentId	id	name	position	level
1073862668	1073862669	L'OREAL PROFESSIONNEL	1	1
1073862668	1073862712	MATRIX	2	1
1073862668	1073862880	KERASTASE	3	1
1073862668	1073871340	ESSIE	4	1
1073862880	1073862899	BUSINESS	0	2
1073862669	1073862670	COLORATION	0	2
1073871340	1073871341	NAILS	1	2
1073862712	1073862726	SPECIALTY	2	2
1073862712	1073862731	SKY IS THE LIMIT	3	2
1073862899	1073862900	7K STANDARDS	0	3
1073862670	1073862671	BLONDE STUDIO	0	3
1073862726	1073862727	PALETTE OF PASSION	0	3
1073862731	1073862732	TOTAL LOOK	0	3
1073862899	1073862901	BUSINESS BOOSTER CLASS 2	1	3
1073862670	1073862672	COLOR CORRECTION	1	3
1073862731	1073862733	CREATIVE SHAPE	1	3
1073871341	1073871342	NATIONAL NAIL POLISH DAY	1	3
1073862726	1073862728	UNBREAKABLE BLONDE	1	3
1073862670	1073862673	COLOR KEYS 1 - DAY 1	2	3
1073862726	1073862729	MAKE ME DIGITAL	2	3
1073862731	1073862734	PHOTOSHOOT	2	3
1073871341	1073871343	UPSKILLING DAY	2	3
1073862670	1073862674	COLOR KEYS 1 - DAY 2	3	3
1073871341	1073871344	ESSIE BRAND EDUCATION	3	3
1073862899	1073862903	NEW BRAND IMMERSION	3	3
1073862670	1073862675	COLOR KEYS 2 - DAY 1	4	3
1073871341	1073871345	ESSIE PRODUCT KNOWLEDGE	4	3
1073862899	1073862904	TREATMENT EXELLENCE	4	3
1073862670	1073862676	COLOR KEYS 2 - DAY 2	5	3
1073871341	1073871346	NAIL ANATOMY AND PHYSIOLOGY	5	3
1073862899	1073862905	SCALP SCIENCE	5	3
1073871341	1073871347	BASIC NAIL ANATOMY	6	3
1073862899	1073862906	HAIR SCIENCE	6	3
1073871341	1073871348	HEALTH & SAFETY	7	3
1073862899	1073862907	LUXURY TRAINING	7	3
1073871341	1073871349	HISTORY AND EVOLUTION OF THE NAIL INDUSTRY	8	3
1073862899	1073862908	SOCIAL MEDIA UPSKILLING	8	3
1073862899	1073862909	SOFT SKILLS IN S2	9	3

The expected result is like below, Brand should contain Level 1, Category should contain level 2, Topic should contain level 3

Brand	Category	Topic	Topic
L'OREAL PROFESSIONNEL	COLORATION	BLONDE STUDIO	BLONDE STUDIO
L'OREAL PROFESSIONNEL	COLORATION	COLOR CORRECTION	COLOR CORRECTION
L'OREAL PROFESSIONNEL	COLORATION	COLOR KEYS 1 - DAY 1	COLOR KEYS 1 - DAY 1
L'OREAL PROFESSIONNEL	COLORATION	COLOR KEYS 1 - DAY 2	COLOR KEYS 1 - DAY 2
L'OREAL PROFESSIONNEL	COLORATION	COLOR KEYS 2 - DAY 1	COLOR KEYS 2 - DAY 1
L'OREAL PROFESSIONNEL	COLORATION	COLOR KEYS 2 - DAY 2	COLOR KEYS 2 - DAY 2
MATRIX	SPECIALTY	PALETTE OF PASSION	PALETTE OF PASSION
MATRIX	SPECIALTY	UNBREAKABLE BLONDE	UNBREAKABLE BLONDE
MATRIX	SPECIALTY	MAKE ME DIGITAL	MAKE ME DIGITAL
MATRIX	SKY IS THE LIMIT	TOTAL LOOK	TOTAL LOOK
MATRIX	SKY IS THE LIMIT	CREATIVE SHAPE	CREATIVE SHAPE
MATRIX	SKY IS THE LIMIT	PHOTOSHOOT	PHOTOSHOOT
KERASTASE	BUSINESS	7K STANDARDS	7K STANDARDS
KERASTASE	BUSINESS	BUSINESS BOOSTER CLASS 2	BUSINESS BOOSTER CLASS 2
KERASTASE	BUSINESS	NEW BRAND IMMERSION	NEW BRAND IMMERSION
KERASTASE	BUSINESS	TREATMENT EXELLENCE	TREATMENT EXELLENCE
KERASTASE	BUSINESS	SCALP SCIENCE	SCALP SCIENCE
KERASTASE	BUSINESS	HAIR SCIENCE	HAIR SCIENCE
KERASTASE	BUSINESS	LUXURY TRAINING	LUXURY TRAINING
KERASTASE	BUSINESS	SOCIAL MEDIA UPSKILLING	SOCIAL MEDIA UPSKILLING
KERASTASE	BUSINESS	SOFT SKILLS IN S2	SOFT SKILLS IN S2
ESSIE	NAILS	NATIONAL NAIL POLISH DAY	NATIONAL NAIL POLISH DAY
ESSIE	NAILS	UPSKILLING DAY	UPSKILLING DAY
ESSIE	NAILS	ESSIE BRAND EDUCATION	ESSIE BRAND EDUCATION
ESSIE	NAILS	ESSIE PRODUCT KNOWLEDGE	ESSIE PRODUCT KNOWLEDGE
ESSIE	NAILS	NAIL ANATOMY AND PHYSIOLOGY	NAIL ANATOMY AND PHYSIOLOGY
ESSIE	NAILS	BASIC NAIL ANATOMY	BASIC NAIL ANATOMY
ESSIE	NAILS	HEALTH & SAFETY	HEALTH & SAFETY
ESSIE	NAILS	HISTORY AND EVOLUTION OF THE NAIL INDUSTRY	HISTORY AND EVOLUTION OF THE NAIL INDUSTRY

[v-xinruzhu-msft · Accepted solution]

Hi @athul00000 

You can create three blank queries in power query and put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVXbbqMwEP2VUZ+7EoEUwqOBSbAwNrJNG9T2M/b/d4Zbw2VDHyIxsY/PxWP78/PlFCTRJQ7j+PLy+lOkVKivv0EQJsaiUNBYc0XnpNEaFc/sf9+v+wskp5CKWngr7/QRPp18uQRUVGiF88IhfUdP5ien6MzzWQzPPa/nDstNRcpOstZJTQD6DHo5i6XTB+MJj+dGGSs8ed0CJvq54CC0kMqNoSwWH3KYijCmwjWYS6F8N+byZH7E67mqA+nAlwhK1tKP8Sxgg8uxSAPWl1RAaepC2GKyHS1sJ8GjbWbKlNEFEqwtpNnDDAbmIqGiEQq9RzBXaETfHrvA3slcsEdvPHWVMqbaA6wMnR72EDJjnEcLuSJCCMfYn5kLpz2F3FiL+bizG9hKJsecU/N7+U6plKLBLWjqgLkI+3ZgBrLHfQGNUdKVUIhul3OZKbd5qzNirUSmEIY9+YXHaPZYYefgBH+YEU5jlz3jTPujWiHUCIW8SdqYXdQyHT53TWm8caUxfgvYJMMK28ZVUimpb2MeG5aVrfO+rXA8Bc/4ztMlAZmlgwBYtPl0qKM17arheFzjx4iUdY3W/Qe5Evy2FBw+7MP5QPDbLJguWxLrodLmQ2Fxwy16I5nHPXdrjdoD3lEp1PkuciU53pfMGb8dSI7Hyw8E9bypO+C4mrKjsJS5ddsVNrJ53OVCNeByOQo+YuWLJxNO5vDITX/GB2Q8XgppH7jiAy4+kCU9gL6E/jmMwYkr9rd3csDG46q9t7YDb4XU1PVb1IaQlyil88YOaeK7US23Ld+x/Ar0nqUuWucty7gcyOBxZ/jNoQNeSAE/h/AX6LRHXz30GEfE4Lgz0gH3/Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [parentId = _t, id = _t, name = _t, position = _t, level = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"parentId", "parentid"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"parentid", Int64.Type}, {"id", Int64.Type}, {"position", Int64.Type}, {"level", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([level] = 1))
in
    #"Filtered Rows"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVXbbqMwEP2VUZ+7EoEUwqOBSbAwNrJNG9T2M/b/d4Zbw2VDHyIxsY/PxWP78/PlFCTRJQ7j+PLy+lOkVKivv0EQJsaiUNBYc0XnpNEaFc/sf9+v+wskp5CKWngr7/QRPp18uQRUVGiF88IhfUdP5ien6MzzWQzPPa/nDstNRcpOstZJTQD6DHo5i6XTB+MJj+dGGSs8ed0CJvq54CC0kMqNoSwWH3KYijCmwjWYS6F8N+byZH7E67mqA+nAlwhK1tKP8Sxgg8uxSAPWl1RAaepC2GKyHS1sJ8GjbWbKlNEFEqwtpNnDDAbmIqGiEQq9RzBXaETfHrvA3slcsEdvPHWVMqbaA6wMnR72EDJjnEcLuSJCCMfYn5kLpz2F3FiL+bizG9hKJsecU/N7+U6plKLBLWjqgLkI+3ZgBrLHfQGNUdKVUIhul3OZKbd5qzNirUSmEIY9+YXHaPZYYefgBH+YEU5jlz3jTPujWiHUCIW8SdqYXdQyHT53TWm8caUxfgvYJMMK28ZVUimpb2MeG5aVrfO+rXA8Bc/4ztMlAZmlgwBYtPl0qKM17arheFzjx4iUdY3W/Qe5Evy2FBw+7MP5QPDbLJguWxLrodLmQ2Fxwy16I5nHPXdrjdoD3lEp1PkuciU53pfMGb8dSI7Hyw8E9bypO+C4mrKjsJS5ddsVNrJ53OVCNeByOQo+YuWLJxNO5vDITX/GB2Q8XgppH7jiAy4+kCU9gL6E/jmMwYkr9rd3csDG46q9t7YDb4XU1PVb1IaQlyil88YOaeK7US23Ld+x/Ar0nqUuWucty7gcyOBxZ/jNoQNeSAE/h/AX6LRHXz30GEfE4Lgz0gH3/Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [parentId = _t, id = _t, name = _t, position = _t, level = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"parentId", "parentid"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"parentid", Int64.Type}, {"id", Int64.Type}, {"position", Int64.Type}, {"level", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([level] = 2)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"parentid"}, Query1, {"id"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"name"}, {"Query1.name"})
in
    #"Expanded Query1"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVXbbqMwEP2VUZ+7EoEUwqOBSbAwNrJNG9T2M/b/d4Zbw2VDHyIxsY/PxWP78/PlFCTRJQ7j+PLy+lOkVKivv0EQJsaiUNBYc0XnpNEaFc/sf9+v+wskp5CKWngr7/QRPp18uQRUVGiF88IhfUdP5ien6MzzWQzPPa/nDstNRcpOstZJTQD6DHo5i6XTB+MJj+dGGSs8ed0CJvq54CC0kMqNoSwWH3KYijCmwjWYS6F8N+byZH7E67mqA+nAlwhK1tKP8Sxgg8uxSAPWl1RAaepC2GKyHS1sJ8GjbWbKlNEFEqwtpNnDDAbmIqGiEQq9RzBXaETfHrvA3slcsEdvPHWVMqbaA6wMnR72EDJjnEcLuSJCCMfYn5kLpz2F3FiL+bizG9hKJsecU/N7+U6plKLBLWjqgLkI+3ZgBrLHfQGNUdKVUIhul3OZKbd5qzNirUSmEIY9+YXHaPZYYefgBH+YEU5jlz3jTPujWiHUCIW8SdqYXdQyHT53TWm8caUxfgvYJMMK28ZVUimpb2MeG5aVrfO+rXA8Bc/4ztMlAZmlgwBYtPl0qKM17arheFzjx4iUdY3W/Qe5Evy2FBw+7MP5QPDbLJguWxLrodLmQ2Fxwy16I5nHPXdrjdoD3lEp1PkuciU53pfMGb8dSI7Hyw8E9bypO+C4mrKjsJS5ddsVNrJ53OVCNeByOQo+YuWLJxNO5vDITX/GB2Q8XgppH7jiAy4+kCU9gL6E/jmMwYkr9rd3csDG46q9t7YDb4XU1PVb1IaQlyil88YOaeK7US23Ld+x/Ar0nqUuWucty7gcyOBxZ/jNoQNeSAE/h/AX6LRHXz30GEfE4Lgz0gH3/Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [parentId = _t, id = _t, name = _t, position = _t, level = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"parentId", "parentid"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"parentid", Int64.Type}, {"id", Int64.Type}, {"position", Int64.Type}, {"level", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([level] = 3)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"parentid"}, Query2, {"id"}, "Query2", JoinKind.LeftOuter),
    #"Expanded Query2" = Table.ExpandTableColumn(#"Merged Queries", "Query2", {"name", "Query1.name"}, {"Query2.name", "Query2.Query1.name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query2",{"parentid", "id", "position", "level"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"name", "Topic"}, {"Query2.name", "Category"}, {"Query2.Query1.name", "Brand"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Brand", "Category", "Topic"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Brand", Order.Ascending}})
in
    #"Sorted Rows"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @athul00000 ,

Check the links below:

https://www.daxpatterns.com/parent-child-hierarchies/

https://data-mozart.com/finding-the-right-path-understanding-parent-child-hierarchies-in-power-bi/

https://www.youtube.com/watch?v=YiTyWUeuwKI