Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
99 | |
90 | |
85 | |
74 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |