Conditional accumulative in Power Query

[dariaglb]

Hello, please help )

I need to change value (text type) from mapping table. But it is important to keep the filter  (not to use Merge table 😞 )

what I have

Table1

Table 2

I applied the solution:

= List.Accumulate(
{0..List.Count(Table2[number])-1},
#"previous step",
(state, current) => Table.ReplaceValue (state,Table2[number]{current},

Table2[decode]{current}, Replacer.ReplaceText,{"Zones"}
) )

and i have this    : -(

but i need to use filter according to crf_form_id 

thanks in advance 

[tharunkumarRTK]

@dariaglb 
From what I understand, you want to add zones from table 2 to table 1 with a join condition based on two columns 
you can do it power query, please follow the steps mentioned here: https://community.fabric.microsoft.com/t5/Power-Query/Join-on-multiple-columns-using-Power-query/m-p...

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[dufoq3]

Hi, It is a bit confusing. Do you want to add Zones from Table2 to Table1  based on [keys crf_form_id] and [number]?
Provide expected results for some rows please.

[dariaglb]

Thank you for your message, I tried to depict

result

[dufoq3]

You can achieve this wth Merge Queries

[dariaglb]

yes, it'll work... but the table has a lot of columns and the task requires not to use the function Merge

[AlienSx · Accepted solution]

not very performant but one step

    replace = Table.ReplaceValue(
        Table1, 
        (o) => o[crf_form_id],
        (n) => n[Zones],
        (v, o, n) => 
            try Table2{[crf_form_id = o, number = n]}[decode]
            otherwise null,
        {"Zones"}
    )

[dufoq3]

If you don't want use Merge, you can achieve the same by adding this as custom column to Table1.

Table.SelectRows(Table2, (x)=> x[crf_form_id] = [crf_form_id] and x[number] = [Zones])[decode]{0}?

To be honest I don't understand why don't you want use merge - it is faster solution.