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.
Hello I'm hopeful someone can assist with transforming a product line that has multiple pack sizes in one column into multiple rows. I believe this can be done in Power Query but I have been unable to find the instructional video demostrating the steps required.
Current Input:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg, 15kg, 20kg |
Desired Output:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 15kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 20kg |
Many thanks in advance for any assistance.
Solved! Go to Solution.
Hi @Baileyleeg,
Please use the split column feature
Result
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
Thanks @Greg_Deckler I appreciate your quick response. In the database there are 1000s of row items as follows. Would the split and unpivot work in this circumstance? Thanks
Input:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg, 15kg, 20kg |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 5L, 10L, 20L, 110L, 200L, 1000L |
Desired Output:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 15kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 20kg |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 5L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 10L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 20L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 110L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 200L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 1000L |
Thanks @Greg_Deckler I appreciate your quick response. In the database there are 1000s of row items as follows. Would the split and unpivot work in this circumstance? Thanks
Input:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg, 15kg, 20kg |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 5L, 10L, 20L, 110L, 200L, 1000L |
Desired Output:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 15kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 20kg |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 5L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 10L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 20L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 110L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 200L |
FUHUA GLYPHOSATE 450 HERBICIDE | SICHUAN LESHAN | FUHUA | glyphosate as ipa(450g/L) | SL | 1000L |
Hi @Baileyleeg,
Please use the split column feature
Result
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
@Baileyleeg So in Power Query, you would generally split that column and then unpivot the resulting columns.
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |