How to replace one item with a different one – apply transformation?

[apoje]

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

[shaowu459 · Accepted solution]

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

 

[apoje]

@shaowu459 awesome! Do you know on the upper limit for lines of data to be still acceptable to use PQ?

[shaowu459]

This one is faster than the Record one.

let
    Source = Excel.CurrentWorkbook(){[Name="dataset"]}[Content],
    res = Table.ToList(Source,each if _{1}="0527" then {_{0},1999,_{2}*10} else _)
in
    Table.FromRows(res,{"Order.ID","Variation.Number","Quantity"})

[shaowu459]

I don't know the exact upper limit, But  several million lines would not be problem. For large data, you may import and export from CSV, access, sql, etc.