Forum Discussion
Need help in data preparation
Hi ,
I have multiple relationship table around 5 to 6 which are available in mutiple tables.
I'm attaching some sample data there are 3 tables table1, table2, table3.
Realtionship is from parent class to child class
business service->business service line -> department -> IT.
Can someone please help me in the data prepartion ?
https://docs.google.com/spreadsheets/d/1kQODqFakeG88QsMegk5URHtKLiZLyUq-/edit?usp=drive_link&ouid=108522559708375813161&rtpof=true&sd=true
Hi DiKi-I ,
You can use pivot columns and merger queries in Power Query.
Here's the solution.
1.Start by adding an index column to each table. This index column is designed to prevent errors when pivoting with multiple elements in a row.
2.Pivot columns in each tables.
Take the first table as an example, select the Parentname column and click Pivot Column.
Seleclt Parent column as values and select 'Don't Aggregate'.
Repeat the above steps to get three tables as follows:
3.Merge them.
4.Expand the column contains tables and merge again.
Expand it again.
5.For the same columns, you can merge them.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi DiKi-I ,
You can use pivot columns and merger queries in Power Query.
Here's the solution.
1.Start by adding an index column to each table. This index column is designed to prevent errors when pivoting with multiple elements in a row.
2.Pivot columns in each tables.
Take the first table as an example, select the Parentname column and click Pivot Column.
Seleclt Parent column as values and select 'Don't Aggregate'.
Repeat the above steps to get three tables as follows:
3.Merge them.
4.Expand the column contains tables and merge again.
Expand it again.
5.For the same columns, you can merge them.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
