Forum Discussion
apoje
Helper II
Helper IIHow to replace one item with a different one – apply transformation?
Hi,
I would like to transform one of my items in the database based on its variation number.
The end result would be a transformation of the variation number and multiplication of its quantity column.
Initial data - I want to apply the transformation on the variation 0527:
| Order.ID | Variation.Number | Quantity |
| 123 | 0527 | 5 |
| 124 | 1234 | 10 |
| 125 | 1239 | 1 |
dataset with the applied transformation (variation number 0527 was transformed to 1999 and its quantity multiplied by 10):
| Order.ID | Variation.Number | Quantity |
| 123 | 1999 | 50 |
| 124 | 1234 | 10 |
| 125 | 1239 | 1 |
Would something like that be possible?
Also, my data set is 100000 lines – I imagine this is not too much for PQ 😊
Thanks for the help!
Andraz
Hi, apoje
Try this, for 100,000 lines please wait about 20 seconds to upload to excel.
let Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content], Custom1 = Table.FromRecords(List.Transform(Table.ToRecords(Source),each if [Variation.Number]="0527" then _&[Variation.Number=1999]&[Quantity=[Quantity]*10] else _)) in Custom1
4 Replies
shaowu459Resolver II
Hi, apoje
Try this, for 100,000 lines please wait about 20 seconds to upload to excel.
let Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content], Custom1 = Table.FromRecords(List.Transform(Table.ToRecords(Source),each if [Variation.Number]="0527" then _&[Variation.Number=1999]&[Quantity=[Quantity]*10] else _)) in Custom1
