Need help in data preparation

[DiKi-I]

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=10...

[Anonymous · Accepted solution]

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.           

[DiKi-I]

@spinfuzer 

[DiKi-I]

@AlienSx