Forum Discussion
Solved
Duplicate rows based on a column
Hi I have this table below and I want to transform the table to have individual records for each country
| country | Fruit Name | Weight (kg) |
| Singapore; Philippines | Apple | 5 |
| Malaysia | Banana | 2 |
| India | Mango | 3 |
| Singapore; India | Kiwi | 7 |
| China | Pineapple | 6 |
| China; HK; Indonesia | Orange | 3 |
| Taiwan | Blueberry | 2 |
| Korea | Raspberry | 1 |
| Japan | Papaya | 4 |
this is my intended outcome
| country | Fruit Name | Weight (kg) |
| Singapore | Apple | 5 |
| Philippines | Apple | 5 |
| Malaysia | Banana | 2 |
| India | Mango | 3 |
| Singapore | Kiwi | 7 |
| India | Kiwi | 7 |
| China | Pineapple | 6 |
| China | Orange | 3 |
| HK | Orange | 3 |
| Indonesia | Orange | 3 |
| Taiwan | Blueberry | 2 |
| Korea | Raspberry | 1 |
| Japan | Papaya | 4 |
How can I do this in power bi?
Hi kellyylx
In Power Query please try the following
- Highlight the country column and go to the Transform tab in the ribbon and choose Split column > By delimiter
- In the Select the delimiter used field, choose custom and enter ; and then space and ok
- Highlight all country columns and choose unpivot columns
Hope this helps
Joe
3 Replies
Hi kellyylx
In Power Query please try the following
- Highlight the country column and go to the Transform tab in the ribbon and choose Split column > By delimiter
- In the Select the delimiter used field, choose custom and enter ; and then space and ok
- Highlight all country columns and choose unpivot columns
Hope this helps
Joehi thanks for the solution, I realised i missed out something in my original table where I have the country code for the country
country code country Fruit Name Weight (kg) A; C Singapore; Philippines Apple 5 B Malaysia Banana 2 D India Mango 3 A; D Singapore; India Kiwi 7 E China Pineapple 6 E; P; L China; HK; Indonesia Orange 3 G Taiwan Blueberry 2 H Korea Raspberry 1 O Japan Papaya 4 and my intended outcome is
country code country Fruit Name Weight (kg) A Singapore Apple 5 C Philippines Apple 5 B Malaysia Banana 2 D India Mango 3 A Singapore Kiwi 7 D India Kiwi 7 E China Pineapple 6 E China Orange 3 P HK Orange 3 L Indonesia Orange 3 G Taiwan Blueberry 2 H Korea Raspberry 1 O Japan Papaya 4 you can assume that the order of the country code and country is the same (eg E; P; L for China; HK; Indonesia means that China - C, HK - P and Indonesia - L)
how can i go about doing this?
Before you Unpivot the columns,
- please repeat the process for the country codes.
- Then highlight the country code 1 and country 1 columns and in the transform tab choose merge columns and choose Tab as a separator. Repeat for the other countires.
- Then unpivot the combined columns
- Highlight the new column go to the Transform tab and choose split column by dilimter and choose Tab
Hope this helps
