Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dariaglb
Frequent Visitor

Conditional accumulative in Power Query

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

dariaglb_1-1713687305256.png

Table 2

dariaglb_2-1713687337018.png

 

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    : -(

dariaglb_0-1713692274713.png

 

 

but i need to use filter according to crf_form_id 

 

thanks in advance 

 

1 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"}
    )

View solution in original post

7 REPLIES 7
tharunkumarRTK
Solution Sage
Solution Sage

@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
Super User
Super User

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you for your message, I tried to depict

dariaglb_0-1713691856740.png

result

dariaglb_1-1713691878495.png

 

You can achieve this wth Merge Queries

 

dufoq3_0-1713693239512.png

 

dufoq3_1-1713693277903.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

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"}
    )

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors