Hierarchy/ Tree chart data clean up

[harshadrokade]

HI All,

I have the multiple company data in format as per 'Original data' table in excel file..

I want to bring this data in 'Transformed data' format as per given levels.

Need your help on how to trasnform this data in seperate columns so that I can use the same in Tree or Hieararchy charts.

If there is any way to use this original data itself as is in any Tree or Hierarchy visual, it would also be helpful.

[harshadrokade]

Original data
OneSourceCompanyName	Level Company Name	level	Holding company Name	% Of holding
A limited		1	B limited	100.00
A limited		1	C limited	60.00
A limited	C limited	2	D limited	40.00
A limited	B limited	2	E limited	60.00
A limited	B limited	2	F limited
A limited	D limited	3	G limited	80.00
A limited	D limited	3	H limited

Transformed data for Tree type visuals
	Level converted in column manually as the visual needs the hirarchy companies in columns
Main company	1	2	3
A limited	B limited	E limited
A limited	B limited	F limited
A limited	C limited	D limited	G limited
A limited	C limited	D limited	H limited

[v-kkf-msft · Accepted solution]

Hi @harshadrokade ,

Please try the following code in Power Query Editor.

let
    Source = Table.SelectColumns(Table.SelectRows(Table,each [level]=1),{"OneSourceCompanyName","Holding company Name"}),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"OneSourceCompanyName", "Main company"}, {"Holding company Name", "1"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "2", each (let currentCustomer = [1] in Table.SelectRows(Table, each [Level Company Name] = currentCustomer))[Holding company Name]),
    #"Expanded 2" = Table.ExpandListColumn(#"Added Custom", "2"),
    #"Added Custom1" = Table.AddColumn(#"Expanded 2", "3", each (let currentCustomer = [2] in Table.SelectRows(Table, each [Level Company Name] = currentCustomer))[Holding company Name]),
    #"Expanded 3" = Table.ExpandListColumn(#"Added Custom1", "3")
in
    #"Expanded 3"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[harshadrokade]

Thanks a lot sir. 

Can you pls tell me the suitable chart to show the company hierancy. I want to show a chart like family tree where the chart will show the family tree for the company selected on slicer. 

Like A - B - E - K

                      - L

                   F

             C - G

                   H

              D - I

                    J

A is main parent, then below comes B, C & D.

Below B, we have E & F.

E further has K & L below it

[v-kkf-msft]

Hi @harshadrokade ,

Please try the Matrix visual.

       

                  

Best Regards,
Winniz