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. 

     

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. 

 

  • Justas4478's avatar
    Justas4478
    Post Prodigy
    1 year ago

    AlienSx It works, thanks for all the help.