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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LucaA
Helper IV
Helper IV

Conditional Query Merge

Hi,

i would like to merge (not merge as new) table1 with a table2 according to a conditional rule applied to another column of table1

so if table1 

1 a

2 a

3 a

4 b

and table2

a 15

b 7

// if colmn 1 = a then merge else put a fixed amount

--> i would like to have table1 with an additional column

1 a Table

2 a Table

3 a Table

4 b (fixed amount not related to table2)

 

can you please help me to achieve this task? Does this approach allow a huge compression of the calculation time or not (i dont want to apply the merge to all the rows because the calculation takes too much time)?

thanks

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @LucaA ,

 

There is a way to do this but it's fairly complex if you're not comfortable with M code, and would also require a significant amount of memory as it involves buffering one table to compare to the other.

 

To your point: no, only conditionally merging some values will not give you any processing gains, as every single row needs to be evaluated regardless.

 

On balance, I think just doing the full merge then doing a conditional Replace Values afterwards would be the most prudent method.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
watkinnc
Super User
Super User

Actually it's not complex at all if you just think it through! You just need to duplicate then separate the first table into a's and everything else, and then join on a to the lookup table, then append that to the table that's not = a.

 

First step named TheAs:

 

= Table.SelectRows (Source, each [Column1] = "a")


next step named TheRest:

 

=Table.SelectRows(Source, each [Column1] <> "a")

 

Add you constants value column to TheRest, call this step Table2:

 

= Table.AddColumn(TheRest, each "Constant Value")

 

Then make a new step named Table1A:

 

= Table.Join(TheAs, {"Column1"}, LookupTable, {"Column1"}, JoinKind.Inner)

 

Expand the table columns that you need. If you need to rename columns or change types, do that now. Columns in TheRest and TheAs have to have the same number of columns, and be of matching column types. So whenever you have your table with the a's looking how you want it, and let's say that the current final step is named FinalStep. Now all you have to do is reach back into your steps and append FinalStep to Table2:

 

= Table.Combine({FinalTable, Table2})

 

And you are done. The thing to remember that makes it easy is that the steps in Power Query can refer to each other in any order, and every step name can be used like a table in your functions (except for circular references).

 

--Nate

 

 

 

Expand the first table that was joined, and expand whichever 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
v-yingjl
Community Support
Community Support

Hi @LucaA ,

Agree with BA_Pete, the better workaround is merge queries and apply conditional rules on replace value.

Refer this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjKCs4zhLBMgK0kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, Table2, {"Name"}, "Table2", JoinKind.LeftOuter),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Queries", each [Table2], each if [Name] <> "a" then [Table2][Value]{0} else [Table2],Replacer.ReplaceValue,{"Table2"})
in
    #"Replaced Value"

vyingjl_0-1631511638550.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BA_Pete
Super User
Super User

Hi @LucaA ,

 

There is a way to do this but it's fairly complex if you're not comfortable with M code, and would also require a significant amount of memory as it involves buffering one table to compare to the other.

 

To your point: no, only conditionally merging some values will not give you any processing gains, as every single row needs to be evaluated regardless.

 

On balance, I think just doing the full merge then doing a conditional Replace Values afterwards would be the most prudent method.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors