Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

Selective replace mcode

Hi, I have this table where employees do not have warehouse attached to them.
There are multiple reason why that could not be the case, but becasue of that I had to group them all as "Unknown"

I can get gate numbers from other sources, but there is chance that in the future ther gate numbers will show up in source data.
Untill then I need temporary solution that replaces unknown value for specific employees.
So for example I would want to replace Warehouse value for employee 21000361 to "Gate 2" only if it is "Unknown", next for employee 21007164 to "Gate 5" if "Unknown" else keep original value.
In total there is aproximately 14 employees that I need to do this for.
I dont know any simple way to solve this since employees numbers are different and gates can differ.
Thanks.

Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago

    the syntax you are using is wrong. Suppose you have a query called your_table that brings you this initial table: 

     Create blank query, open advanced editor and replace everything inside it with the following code: 

    Table.ReplaceMatchingRows(
    your_table, 
    {
        {[Staff Number = 21000361, Warehouse = "Unknown"], [Staff Number = 21000361, Warehouse = "Gate 2"]},
        {[Staff Number = 21007164, Warehouse = "Unknown"], [Staff Number = 21007164, Warehouse = "Gate 5"]}
    }
)

    Result is: 

    Make sure you know what are lists and records as well as list of records and their syntax in M. Otherwise it won't be easy for you to implement and support my solution. 

     

6 Replies

    • Justas4478's avatar
      Justas4478
      Post Prodigy
      1 year ago

      AlienSx I tried to use function that you suggested.
      I am not getting any sintax errors.

      Table.ReplaceMatchingRows(Table.FromRecords({
      [Staff Number] = 21000361, [Warehouse] = "Unknown",
      [Staff Number] = 21007164, [Warehouse] = "Unknown",
      [Staff Number] = 21010743, [Warehouse] = "Unknown",
      [Staff Number] = 21016747, [Warehouse] = "Unknown",
      [Staff Number] = 21016756, [Warehouse] = "Unknown",
      [Staff Number] = 80400417, [Warehouse] = "Unknown",
      [Staff Number] = 80400519, [Warehouse] = "Unknown",
      [Staff Number] = 80419845, [Warehouse] = "Unknown",
      [Staff Number] = 80419925, [Warehouse] = "Unknown"
      }),
      {
      {[Staff Number] = 21000361, [Warehouse] = "Unknown", [Staff Number] = 21000361, [Warehouse] = "Gate 2"},
      {[Staff Number] = 21007164, [Warehouse] = "Unknown", [Staff Number] = 21007164, [Warehouse] = "Gate 5"},
      {[Staff Number] = 21010743, [Warehouse] = "Unknown", [Staff Number] = 21010743, [Warehouse] = "Gate 5"},
      {[Staff Number] = 21016747, [Warehouse] = "Unknown", [Staff Number] = 21016747, [Warehouse] = "Gate 2"},
      {[Staff Number] = 21016756, [Warehouse] = "Unknown", [Staff Number] = 21016756, [Warehouse] = "Gate 3"},
      {[Staff Number] = 80400417, [Warehouse] = "Unknown", [Staff Number] = 80400417, [Warehouse] = "Gate 2"},
      {[Staff Number] = 80400519, [Warehouse] = "Unknown", [Staff Number] = 80400519, [Warehouse] = "Gate 5"},
      {[Staff Number] = 80419845, [Warehouse] = "Unknown", [Staff Number] = 80419845, [Warehouse] = "Gate 2"},
      {[Staff Number] = 80419925, [Warehouse] = "Unknown", [Staff Number] = 80419925, [Warehouse] = "Gate 2"}
      }
      )

      But I do get this error when it runs

       



      • AlienSx's avatar
        AlienSx
        Super User
        1 year ago

        the syntax you are using is wrong. Suppose you have a query called your_table that brings you this initial table: 

         Create blank query, open advanced editor and replace everything inside it with the following code: 

        Table.ReplaceMatchingRows(
    your_table, 
    {
        {[Staff Number = 21000361, Warehouse = "Unknown"], [Staff Number = 21000361, Warehouse = "Gate 2"]},
        {[Staff Number = 21007164, Warehouse = "Unknown"], [Staff Number = 21007164, Warehouse = "Gate 5"]}
    }
)

        Result is: 

        Make sure you know what are lists and records as well as list of records and their syntax in M. Otherwise it won't be easy for you to implement and support my solution. 

         

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    Hello Justas4478 

     

    Do you have any specific criteria for assigning warehouse for each staff id.
    it it is then  please use that sheet for merging the main source.
    Below are the steps
    1) Create a separate sheet where all the staff id has warehouse number.
    2) Merge that sheet with main source table

    3) Create a custom coulumn.

    Below is the PQ code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAUMlHaXQvOy8/PI8pVgdmKgRVlFjrKImWEVNsYqaYRU1xypqgVXUEijqnliSqmBoihA1NMCm1hCr3wyN4CZYIIli9ZshVr8ZmsJMMDJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StaffNumber = _t, Warehouse = _t]),
    Merged = Table.NestedJoin(Source, {"StaffNumber"}, WarehouseSheet, {"StaffNumber"}, "MergedTable", JoinKind.LeftOuter),
    AddedCustom = Table.AddColumn(Merged, "WarehouseFinal", each if [Warehouse] = "Unknown" then [MergedTable]{0}[Warehous] else [Warehouse]),
    RemovedColumns = Table.SelectColumns(AddedCustom, {"StaffNumber", "Warehouse", "WarehouseFinal"})
in
    RemovedColumns

    Below screenshot

     

    Regards

    sanalytics

    If it is your solution then please like and accept it as solution