Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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 |
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |