Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find and replace one specific value in a row

Hi everyone, In Power BI, I am currently using a table with about 8M transactions from an SQL query. For one very specific transaction, I want to change the SKU name and replace it with a differen...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    If you are sure there is only one record matching criteria, you can use this line of code:

     

    = Table.ReplaceMatchingRows(yourTab, {Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610){0},[name="Client3",amount=610,SKU=newValue]})

     

    Otherwise, in case of multiple rows matching the criteria, try this:

     

     

    Table.ReplaceMatchingRows(yourTab, List.Zip({Table.ToRecords(Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610)), List.Repeat({[name="Client3",amount=610,SKU=newValue]}, List.Count(Table.ToRecords(Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610))))}))

     

    PS

    In case of duplicate rows, seems there is some problems. But I didn't investigate furter .

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Can you provide a data sample so the scenario can be recreated?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

This is very simplified example from a table with 30 columns and Ms of rows. But basically, I need to apply a transformation after the query where I can find the row with the amount "5787", and replace the SKU "ProductABC", for another SKU, eg "ProductXYZ", just for that row - without impacting the other rows. Thanks

SnapshotClientSKUAmount
2020-06-09Client1ProductABC100
2020-06-09Client2ProductABC120
2020-06-09Client3ProductABC90
2020-06-08Client1ProductABC130
2020-06-08Client2ProductABC5787
2020-06-08Client3ProductABC100
2020-06-07Client1ProductABC392
2020-06-07Client2ProductABC400
2020-06-07Client3ProductABC110
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    If you are sure there is only one record matching criteria, you can use this line of code:

     

    = Table.ReplaceMatchingRows(yourTab, {Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610){0},[name="Client3",amount=610,SKU=newValue]})

     

    Otherwise, in case of multiple rows matching the criteria, try this:

     

     

    Table.ReplaceMatchingRows(yourTab, List.Zip({Table.ToRecords(Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610)), List.Repeat({[name="Client3",amount=610,SKU=newValue]}, List.Count(Table.ToRecords(Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610))))}))

     

    PS

    In case of duplicate rows, seems there is some problems. But I didn't investigate furter .

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This should work in both cases:

       

      Table.FromRecords(Table.TransformRows(yourTab, each if ([name]="nome3" and [amount]=5787) then Record.TransformFields(_,{"SKU", (_)=> newValue}) else _))
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        and yet another way to set a field to a specific value:

        Table.FromRecords(Table.TransformRows(yourTab, each if ([name]="nome3" and [amount]=5787) then Record.Combine({_,[SKU=newValue]}) else _))

         

        Also this solution seems valid  both in case of single, multiple or null matching