Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Everyone,
I have employee and manager id data. Below is sample data
emp id | name | manager id | level | top level path id | top level name |
1 | g | 5 | 2 | 5>1 | m>g |
4 | d | 3 | 4 | 5>2>3>4 | m>n>f>d |
2 | n | 5 | 2 | 5>2 | m>n |
3 | f | 2 | 3 | 5>2>3 | m>n>f |
5 | m | 1 | 5 | m |
I'm preparing data for Table visiual to show manager hierarchy till top level of an(filtered single selection) employee.
created custom column for split `top level path id` columns into rows
hierarchy id = Text.Split([#" ""top level path id"""] , ">")
hierarchy names = Text.Split([#" ""top level name"""] , ">")
Table.FromColumns({[hierarchy id],[hierarchy names]})
Now I want level for each `hierarchy id`, so i was merging queries (like self join) like query.`hierarchy id`=query.`emp id`
and expected below result, when expanded columns after merging
id | name | manager id | level | top level path | top level name | hierarchy id | hierarchy names | manager level |
1 | g | 5 | 2 | 5>1 | m>g | 5 | m | 1 |
1 | g | 5 | 2 | 5>1 | m>g | 1 | g | 2 |
4 | d | 7 | 4 | 5>2>7>4 | m>n>f>d | 5 | m | 1 |
4 | d | 7 | 4 | 5>2>7>4 | m>n>f>d | 2 | n | 2 |
4 | d | 7 | 4 | 5>2>7>4 | m>n>f>d | 7 | f | 3 |
4 | d | 7 | 4 | 5>2>7>4 | m>n>f>d | 4 | d | 4 |
2 | n | 5 | 2 | 5>2 | m>n | 5 | m | 1 |
2 | n | 5 | 2 | 5>2 | m>n | 2 | n | 2 |
7 | f | 2 | 7 | 5>2>7 | m>n>f | 5 | m | 1 |
7 | f | 2 | 7 | 5>2>7 | m>n>f | 2 | n | 2 |
7 | f | 2 | 7 | 5>2>7 | m>n>f | 7 | f | 3 |
5 | m | 1 | 5 | m | 5 | m | 1 |
Suprisengly i got `manager level` (level in orginal dataset) column values as nulls.
Can we do merge on custom columns ?
Many thanks to anyone who can help me!
Solved! Go to Solution.
Hi @Hari01
Here is my solution with Power Query. You can create a blank query, open its Advanced editor and paste below code to replace everything there. Click ok to save the query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYwxDsAgDAO/UmVmAdI1H0HZEEzh/yNOCkOHEyi2rzXKlGiCFxR/xS8mkzQ1Yvw7qIAjLVKFo7FkSI+W79bPUb5GpL4dJ6nXcQ3R8J2BB+TjMVLd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"emp id" = _t, name = _t, #"manager id" = _t, level = _t, #"top level path id" = _t, #"top level name" = _t]),
#"Changed column type" = Table.TransformColumnTypes(Source, {{"emp id", Int64.Type}, {"name", type text}, {"manager id", Int64.Type}, {"level", Int64.Type}, {"top level path id", type text}, {"top level name", type text}}),
#"Duplicated column" = Table.DuplicateColumn(#"Changed column type", "top level path id", "hierarchy id"),
#"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated column", {{"hierarchy id", Splitter.SplitTextByDelimiter(">"), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "hierarchy id"),
#"Changed column type 1" = Table.TransformColumnTypes(#"Split column by delimiter", {{"hierarchy id", Int64.Type}}),
#"Merged queries" = Table.NestedJoin(#"Changed column type 1", {"hierarchy id"}, #"Changed column type 1", {"emp id"}, "Changed column type 1", JoinKind.LeftOuter),
#"Added custom" = Table.AddColumn(#"Merged queries", "manager level", each Table.RowCount([Changed column type 1])),
#"Expanded Changed column type 1" = Table.AggregateTableColumn(#"Added custom", "Changed column type 1", {{"name", List.Max, "hierarchy names"}})
in
#"Expanded Changed column type 1"
Hope this helps. Let me know if you have any questions.
----------------------------------------------------------------------
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
Hi @Hari01
Here is my solution with Power Query. You can create a blank query, open its Advanced editor and paste below code to replace everything there. Click ok to save the query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYwxDsAgDAO/UmVmAdI1H0HZEEzh/yNOCkOHEyi2rzXKlGiCFxR/xS8mkzQ1Yvw7qIAjLVKFo7FkSI+W79bPUb5GpL4dJ6nXcQ3R8J2BB+TjMVLd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"emp id" = _t, name = _t, #"manager id" = _t, level = _t, #"top level path id" = _t, #"top level name" = _t]),
#"Changed column type" = Table.TransformColumnTypes(Source, {{"emp id", Int64.Type}, {"name", type text}, {"manager id", Int64.Type}, {"level", Int64.Type}, {"top level path id", type text}, {"top level name", type text}}),
#"Duplicated column" = Table.DuplicateColumn(#"Changed column type", "top level path id", "hierarchy id"),
#"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated column", {{"hierarchy id", Splitter.SplitTextByDelimiter(">"), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "hierarchy id"),
#"Changed column type 1" = Table.TransformColumnTypes(#"Split column by delimiter", {{"hierarchy id", Int64.Type}}),
#"Merged queries" = Table.NestedJoin(#"Changed column type 1", {"hierarchy id"}, #"Changed column type 1", {"emp id"}, "Changed column type 1", JoinKind.LeftOuter),
#"Added custom" = Table.AddColumn(#"Merged queries", "manager level", each Table.RowCount([Changed column type 1])),
#"Expanded Changed column type 1" = Table.AggregateTableColumn(#"Added custom", "Changed column type 1", {{"name", List.Max, "hierarchy names"}})
in
#"Expanded Changed column type 1"
Hope this helps. Let me know if you have any questions.
----------------------------------------------------------------------
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
You may want to read about (and use) the PATH* functions instead.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.