Pivoting multiple columns side by side

[HFG]

Hi all, 

I'm very new to Power Query and don't know much about M so I'm turning to you for help. I have a table of repair tickets with a line per ticket  and multiple "Reason" columns and the same number of "Photo" columns. I am trying to transfer this data to a new normalised database where I have a ticket column, a reason column and a photo column. I am attempting to transform the data in Power Query. In the attached image, A is what I have, B is what I would like and C is where I have ended up by unpivoting the table. Could you help me achieve what I need please ? 

Thanks

[Anonymous · Accepted solution]

Hi @HFG ,

Here's my solution.

Sample data:

1.Duplicate it.

2.In Table, select only reason columns and unpivot them, remove all photo columns.

3.Split Attribute column.

4.Remove Attribute.1 column.

5.In the duplicated table, follow the same steps as before, but keep the Photo columns and remove the Reason columns.

6.Merge them. You need to notice that there are two matching columns.

7.Expand them and remove unneeded columns, rename them.

You can download my attachment for more details.

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.           

[HFG]

Thank you, that worked well.

[AjithPrasath]

Hi,
to get the desired result , please follow the steps below in power query editor:
1. Select only reason columns and unpivot them
2. Remove the attribute column from the table
3. Select photos column and unpivot them
4. Remove the attribute column from the table
5. Select the two value columns and remove the duplicates by right click
6. Change the column names to reason and photos

Output:

If your solution is met please mark this as solution.

Thanks,

AJ

[HFG]

Hi @AjithPrasath , 

Thanks for much that worked really well. The only thing that doesn't seem to have worked is the deleting duplicates. I had 18K lines when I unpivoted just the reasons and now I have 20K and I can see duplicate lines. Do you know any reason why it might not have deleted all the duplicates ?