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" ...
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. 

     

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