Forum Discussion

apoje's avatar
apoje
Icon for Helper II rankHelper II
5 years ago
Solved

How 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 c...
Need Help
  • shaowu459's avatar
    shaowu459
    5 years ago

    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

     

shaowu459's avatar
shaowu459
Icon for Resolver II rankResolver II
5 years ago

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's avatar
apoje
Icon for Helper II rankHelper II
5 years ago

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

  • shaowu459's avatar
    shaowu459
    Icon for Resolver II rankResolver II
    5 years ago

    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.

  • shaowu459's avatar
    shaowu459
    Icon for Resolver II rankResolver II
    5 years ago

    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"})