Selective replace mcode

[Justas4478]

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.

[Omid_Motamedise]

Use the function table.replacevalue

[AlienSx]

Table.ReplaceMatchingRows is your friend.

[Justas4478]

@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 · Accepted solution]

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. 

[Justas4478]

@AlienSx It works, thanks for all the help.

[sanalytics]

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