Join on multiple columns using Power query

[Anonymous]

HI ,

I need to achieve join condition in Power BI similar to sql joins that was generated from other tool. Below is my Join condition that was generated by the tool on the two tables(Sales and Purchase).

purchase.pur_id (+)= Sales.numid and
sales.pcode=purchase.mid(+) and
purchase.frm__dt (+)<= sales.dum_dt and
purchase.to_dt (+) >= sales.dum_dt and
purchase.ven_id (+)||purchase.fixd is not null

Can some Guide me how to achieve this in power BI.

Thanks.

[v-xuding-msft]

Hi @Anonymous ,

Maybe these blog can help you. 

Join Conditions In Power Query, Part 1 

PowerQuery(M)agic: Conditional Joins using Table.SelectRows() 

[edhans]

@Anonymous - you do not need to concatenate in Power Query to join on multiple columns. In the Merge box, just select your first column, then CTRL-CLICK on the 2nd, 3rd, etc.

It will put numbers next to the columns in the order you click.

THen in your 2nd table, click on the same columns you want to merge on in the same order.

There is not a way to join based on <= using the merge feature. It only supports:

• left/right join
• inner join
• outer join
• left/right anti-join

You can do a cartesian join too outside of the merge tool. 

You can do it though with a Table.SelectRows(). It may not perform well on large data sets. Consider you have two tables:

One called Table:

One called Other Data:

The following code, using the Table.SelectRows() function below will "merge" the [Other Data] table into the [Table] table when Data from Other Data is <= Data from Table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApKVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
    #"Other Merge" = 
        Table.AddColumn(
            #"Changed Type",
            "Other Merge",
            each
                let
                    varData = [Data]
                in
            Table.SelectRows(
                #"Other Data",
                each [Data] <= varData
            )
        ),
    #"Expanded Other Merge" = Table.ExpandTableColumn(#"Other Merge", "Other Merge", {"Codes"}, {"Codes"})
    
in
    #"Expanded Other Merge"

You can see my PBIX here if you want to play with it.

[Anonymous]

Thanks @edhans  for the response.

My sales fact table has relationship with multiple tables along with Purchase dim table. So i dont want to go and create multiple merge tables and use it in my model and create reports on top of it.

As my fact table is surrounded by multple dimension tables, I am looking for a better approach to resolve the join condition between the sales table and with the individual tables using the concatenate columns.

So as a first step, i am trying to replicate the join condition that occurs between the fact(Sales) and Purchase table.Like wise i am planning to do the same with fact and other dim tables. so that relation between dim and fact table occurs as 1:M and the data gets filtered accordingly.

Please correct me if my approach is wrong in doing so.

As part of impementation between the tables, I am unable to figure how to implement this logic from 2 tables

purchase.frm__dt (+)<= sales.dum_dt and

purchase.to_dt (+) >= sales.dum_dt

I tried using lookupvalue but unable to achieve on Dim table.

Any suggestion will be highly appreciated.

Thanks in advance.

[edhans · Accepted solution]

@Anonymous I think you might be trying to apply the logic of some other app you are using to Power BI, and that will not work. You don't actually create JOINS in the Power BI data model. You do in Power Query, but not in DAX. In DAX they are filter relationships, and they either equal or they don't.

However, once you have a filter relationship, you can apply other logic in your formulas (measures) to modify how they work. For example, you might have the following to show cumulative sales through a date on your visual:

Sales Cumulative =
VAR varCurrentDate =
    MAX( Sales[Date] )
RETURN
    CALCULATE(
        [Sales],
        Date[Date] <= varCurrentDate,
        REMOVEFILTERS( Dates[Date] )
    )

That will cause the filter to act in a way that will get all sales prior to today through today.

But that is not a merge operation.

Does that make sense? 

Perhaps tell us what your end goal is, and not ask us to translate Program A logic into Power BI logic. It can cause us to come at this totally wrong, which I've certianly being doing so far. 😁

[edhans]

@Anonymous - do you have a solution to this yet? If so, can you mark one or more posts as the solution. If not, can you get back to us with what you are trying to accomplish - the end goal - vs how to accomplish specific steps due to your familiarity with how another app does it?

[swise001]

@Anonymous 

When you have to join on multiple colums - consider adding a custom column where you concatenate the values that are part of your match criteria.   Use Text.From() to convert any numbers into text so they concatenate properly.  

Once you have a column created in each table - you can use it as the basis for your merge - making sure to select that column in each of your two tables.  

[Anonymous]

Thanks @swise001  for the response.

This is helpful as a part of my requirement. My other scenario in this query is how to achieve comparison of different columns as shown below

purchase.pur_id (+)= Sales.numid and
sales.pcode=purchase.mid(+) and
purchase.frm__dt (+)<= sales.dum_dt and
purchase.to_dt (+) >= sales.dum_dt and
purchase.ven_id (+)||purchase.fixd is not null

I will do a concatenate on two columns as you suggested.

purchase.pur_id (+)= Sales.numid and
sales.pcode=purchase.mid(+) and

But how to achieve the below requirement in Power query.

purchase.frm__dt (+)<= sales.dum_dt and
purchase.to_dt (+) >= sales.dum_dt

I need to create a calculated column on the above two conditions and use that to create a relationship between this tables to filter data.

Any help/approach will be highly appreciated. 

Thanks in advance.

[swise001]

@Anonymous 

My suggestion would be to first do the merge and bring over any columns that will be necessary with your conditional logic.  

Then you can add a conditional column where you use this comparitive criteria to 'flag' only those items that meet the criteria.  

Consider breaking it down into small steps (since it's got both AND and OR criteria) - so you may need a few conditional columns to make it work.  

You can also write AND/OR logic into the Add Custom Column box using a format like this: 

[Anonymous]

Thanks @swise001  for the response

My sales fact table has relationship with multiple tables along with Purchase dim table. So i dont want to go and create multiple merge tables and use it in my model and create reports on top of it.

As my fact table is surrounded by multple dimension tables, I am looking for a better approach to resolve the join condition between the sales table and with the individual tables using the concatenate columns.

So as a first step, i am trying to replicate the join condition that occurs between the fact(Sales) and Purchase table.Like wise i am planning to do the same with fact and other dim tables. so that relation between dim and fact table occurs as 1:M and the data gets filtered accordingly.

Please correct me if my approach is wrong in doing so.

As part of impementation between the tables, I am unable to figure how to implement this logic from 2 tables

purchase.frm__dt (+)<= sales.dum_dt and

purchase.to_dt (+) >= sales.dum_dt

I tried using lookupvalue but unable to achieve on Dim table.

Any suggestion will be highly appreciated.

Thanks in advance.