Power BI Conditional Merge

[Anonymous]

Hi all,

I need to join 2 tables based on a column that is not always the same in both tables

The problem:

Table 1 (Book100):

Table 2 (BookSummary):

Now I want to join these tables based on the column named 'Booktype', but as you can see in Table 2 every 'Bookcode' has a 'Typecode' (Bookcode_Typecode). But in Table 1 it can be that some rows only have a Bookcode without having a Typcode.

What I want is, that if 'Booktype' in Table 1 has both 'Bookcode' as 'Typecode' it should merge with the 'Booktype' of Table 2. But if 'Booktype' in Table 1 does not have a 'Typecode' it should merge to all 'Bookcodes' that are in the 'Booktype' column of Table 2 (so only the part before the underscore _).

In a IF statement it would be:
IF([Book100]Typecode = ""; [BookSummary]Booktype ONLY THE PART BEFORE THE _ ; [BookSummary]Booktype)

Advanced editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY3BDcAwCAN3yTsPYqjUrtBmgyhi/y0CTsMH31mCMQqaiLRSyydNYm120qzRqzwW/sWNrBOdwBZQDZ0NY0pTwy933FIt73R+IXk/P3DFnDPMcy4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Bookcode = _t, Typecode = _t, BookType = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Bookcode", Int64.Type}, {"Typecode", type text}, {"BookType", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BookType"}, BookSummary, {"BookType"}, "BookSummary", JoinKind.LeftOuter),
#"Expanded BookSummary" = Table.ExpandTableColumn(#"Merged Queries", "BookSummary", {"Author", "Year"}, {"BookSummary.Author", "BookSummary.Year"})
in
#"Expanded BookSummary"

Hopefully someone can help me change the current merge to a conditional merge, I would appreciate it so much!

Thanks in advance!

[Mariusz · Accepted solution]

Hi @Anonymous 

Try this script, or see the attached for details

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI0MDAwVNJR8jYwNFCK1QGKGBtYmgAFvIwsjCACRkbGxkABMMfE2MQIiWNsAlLqA9NqZAoEYNlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Bookcode = _t, Typecode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bookcode", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
        (a) => Table.SelectRows( 
            BookSummary, 
            (b) => 
                ( a[Bookcode] = b[Bookcode.1] ) and 
                ( if a[Typecode] <> "" then a[Typecode] = b[Typecode.1] else true ) 
            ) 
    )
in
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@MariuszThank you so much! Looking at your PBI file I have managed it to work, greatly appreciated!

[Greg_Deckler]

I'm not entirely sure how you would do that exactly. Maybe use the Fuzzy merge options? @ImkeF @edhans ?

[Anonymous]

Thanks for sharing it with others!

I was hoping that something can be added inside the Advanced Editor, but couldn't figure it out...

[Greg_Deckler]

If anyone will know it is the folks that I referenced. But I was thinking this:

[Anonymous]

I believe that fuzzy matching is only useful when the format is the same for all rows and there are some minor changes/typos in some rows. As for me, the format isn't the same for all rows I am afraid that this won't work in my case.