Creating a Parent ID Key from a flattened table

[Vinxsta]

I have a table as shown below which shows a organisation hierarchy. I'm trying to create a organisation chart but can't do that with the way my data stands at the moment. After some research I found that I would need to create a Parent ID key and then use PATH etc. Please could you advise how i can create a Parent ID key and then what i would need to do to structure my data in the right way so i can use the heriarchy visuals in Power BI, including the dax measures I need to add after. Many thanks in advance!!

EmpName	Level1_mgr	Level2_mgr	Level3_mgr	Level4_mgr	Level5_mgr	Level6_mgr
John	Kevin	Lionel	Mindy	Neve	Oscar	Paulina
Jenny	Kenny	Linda	Maggie	Niall	Ollie	Patrick
James	Kim	Lottie	Max	Nelly	Odi	Paul

[HarishKM]

  Hello,
Kindly refer below documentation.Understanding functions for parent-child hierarchies in DAX - DAX | Microsoft Learn

Thanks 

Harish M

Did I answer your question? Mark my post as a solution!

@Vinxsta

[Ashish_Mathur]

Hi,

Show the expected result in a simple Table format.

[Vinxsta]

Hi Ashish,

Essentially, I want something that looks like the table below. With the current format I'm struggling to use any of the heirarchy visuals to correctly create my org chart.

EmpID	EmpName	ManagerID	Path
1	CEO	1	1
2	Paulina	2	1|2
3	Patrick	2	1|3
4	Paul	2	1|4
5	Oscar	3	1|2|5
6	Ollie	3	1|3|6
7	Odi	3	1|4|7
8	Neve	4	1|2|5|8
9	Niall	4	1|3|6|9
10	Nelly	4	1|4|7|10
11	Mindy	5	1|2|5|8|11
12	Maggie	5	1|3|6|9|12
13	Max	5	1|4|7|10|13
14	Lionel	6	1|2|5|8|11|14
15	Linda	6	1|3|6|9|12|15
16	Lottie	6	1|4|7|10|13|16
17	Kevin	7	1|2|5|8|11|14|17
18	Kenny	7	1|3|6|9|12|15|18
19	Kim	7	1|4|7|10|13|16|19
20	John	8	1|2|5|8|11|14|17|20
21	Jenny	8	1|3|6|9|12|15|18|21
22	James	8	1|4|7|10|13|16|19|22

[lbendlin · Accepted solution]

All you need is an employee table that lists the manager for each employee.

[Vinxsta]

Hi Ibendlin, thanks for doing this. I've changed the source but at the last step I'm getting an error at the last step. The table i pasted in my original comment is not the table of my original source but I've amended the column headers and that was fine. Not sure why I'm geting errors

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

I hope this is not how your actual data looks like. However if it does then you can simple brute force the Path construction.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc1LDoUwCEDRvTB2Jf7q3HRAlPiISBOtRncv1De6bcKBcYQ6/RQqaOhib8tJSezRsc6PtaeLLOGYcLcOeAorQqyMkupT7NfWCDrFZWFHPaP4riBS/gPmnaf1w7jR4Zg3pynnMtLhXY6K+MYw8/8mxPgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmpName = _t, Level1_mgr = _t, Level2_mgr = _t, Level3_mgr = _t, Level4_mgr = _t, Level5_mgr = _t, Level6_mgr = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Path", each #table({"path"},{
    {[Level6_mgr] & "|" & [Level5_mgr] & "|" & [Level4_mgr] & "|" & [Level3_mgr] & "|" & [Level2_mgr] & "|" & [Level1_mgr] & "|" & [EmpName]},
    {[Level6_mgr] & "|" & [Level5_mgr] & "|" & [Level4_mgr] & "|" & [Level3_mgr] & "|" & [Level2_mgr] & "|" & [Level1_mgr]},
    {[Level6_mgr] & "|" & [Level5_mgr] & "|" & [Level4_mgr] & "|" & [Level3_mgr] & "|" & [Level2_mgr]},
    {[Level6_mgr] & "|" & [Level5_mgr] & "|" & [Level4_mgr] & "|" & [Level3_mgr]},
    {[Level6_mgr] & "|" & [Level5_mgr] & "|" & [Level4_mgr]},
    {[Level6_mgr] & "|" & [Level5_mgr]},
    {[Level6_mgr]}})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Path"}),
    #"Expanded Path" = Table.ExpandTableColumn(#"Removed Other Columns", "Path", {"path"}, {"path.1"})
in
    #"Expanded Path"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.