Self Merge Queries on custom columns

[Hari01]

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! 

[liuqi_pbi · Accepted 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!

[lbendlin]

You may want to read about (and use) the PATH* functions instead.