Forum Discussion
DanHuber
Dec 06, 2023Iron Contributor
Split a row into several rows with almost same content. PowerQuery?
Difficult to explain by me as non native english speaker, but I'll give you an example
I have a table looking like this:
Article | Description | Price | E1 | E2 | E3 | P1 | P2 | P3 |
1234 | FirstThingy | 100 | ABCD | EFGH | IJKL | 10 | 100 | 1000 |
1235 | SecondThingy | 200 | MNOP | QRST | 210 | 280 | ||
1236 | ThirdThingy | 400 | UVWX | 15 |
Is itpossible with PowerQuery, to generate the following table?
Article | Description | Price | Ex | Px |
1234 | FirstThingy | 100 | ABCD | 10 |
1234 | FirstThingy | 100 | EFGH | 100 |
1234 | FirstThingy | 100 | IJKL | 1000 |
1235 | SecondThingy | 200 | MNOP | 210 |
1235 | SecondThingy | 200 | QRST | 280 |
1236 | ThirdThingy | 400 | UVWX | 15 |
That is, based on a column in the row, duplicate the row, with all the values except the ones that are different?
Is that possible with PQ? And if yes, how?
(Background: I got an article list from a vendor that does have sub-vendors for some products. The product is the same, but the EAN number (E1,E2,E3) might be different from the sub-vendors. We need to track the goods based on the EAN code printed on the article. Since the same product might have different EAN numbers our ERP system would have to be able to differentiate between the EAN numbers and let them point to the same product.
Just.. our ERP is not able to store and process more than one EAN number per product. The best solution I came up with is to split the vendors article list into several (almost identical) articles, each with the different EAN number, but the rest is the same..
Do I make sense?
Thanks for any hint
Dan
4 Replies
Sort By
- peiyezhuBronze Contributor
with sql,
//select * from Sheet1;
cli_unstack~Sheet1~E,P;
select colExclude[上层] from Sheet1_unstack where regexp('\w+',E);
- DanHuberIron Contributor
I unfortunately do not quite understand that solution.
It looks like this is an example on how to do it with SQL in a webapp?
I was looking for instructions on how to do this in Excel PowerQuery. Can this be done there too?
- Riny_van_EekelenPlatinum Contributor