Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Join Conditions In Power Query, Part 1

with 9 comments

In last week’s post I showed how to create a simple LIKE function in Power Query which could be used in a calculated column. This week I’m going to show you how you can use this function in a condition when joining two tables together.

Consider the following two tables in an Excel worksheet:

image

Let’s say you want to join the Patterns table to the Phrases table, but only return the rows for each pattern where the Like() function returns true. Power Query has good support for different types of joins in the Table.Join() and Table.NestedJoin() functions but it isn’t immediately obvious how to handle join conditions such as this one.

Here’s the M code for a query that shows you how to do this:

let

    //Load Patterns table

    PatternsTable = Excel.CurrentWorkbook(){[Name="Patterns"]}[Content],

    //Load Phrases table

    PhrasesTable = Excel.CurrentWorkbook(){[Name="Phrases"]}[Content],

    //Add custom column to Patterns that returns the Phrases table for each row

    InsertedCustom = Table.AddColumn(PatternsTable, "AllPhrases", each PhrasesTable),

    //Expand the new column

    ExpandedTable = Table.ExpandTableColumn(InsertedCustom, "AllPhrases", {"Phrases"}, {"Phrases"}),

    //Filter the expanded table using the Like() function

    Custom1 = Table.SelectRows(ExpandedTable, each Like([Phrases],[Patterns]))

in

    Custom1

 

What this query does is the following:

  • Gets the data from both the Patterns table and the Phrases table. I’ve done this as two steps in the same query for simplicity; you might want to create two separate queries to do this.
  • On the Patterns table, add a new custom column that returns the entire Phrases table as a value. The Insert Custom Column dialog and its output will look like this:

image

  • Expands the new custom column so that every row of the Phrases table is displayed against every row of the Patterns table – in effect, it does a cross join between the two tables. Interestingly there is no option to do a cross join using the Table.Join() and Table.NestedJoin() functions.

image

  • Finally, it uses the Like() function in Table.SelectRows() as follows:

    Table.SelectRows(ExpandedTable, each Like([Phrases],[Patterns]))

    …to filter the table from the previous step so that you only get the rows back where the Like() function returns true and the pattern matches the phrase:

image

So… we have a solution to our problem (and you can download the example workbook here). However, be warned: for large tables this approach may not perform well! In part two I’ll show you another example of a complex join condition and show you how different approaches can yield very different performance.

Written by Chris Webb

June 2, 2014 at 9:17 pm

Posted in Power Query

9 Responses

Subscribe to comments with RSS.

  1. Awesome, Chris
    Once we figure out how to use stuff like this, life for Excel Power users like me (30 is never going to be the same again!

    dave white

    June 3, 2014 at 11:59 am

  2. […] my last post you saw how to join two tables together using conditions other than the built-in inner, outer and […]

  3. […] my last post you saw how to join two tables together using conditions other than the built-in inner, outer and […]

  4. Hi Chris, I didn’t know where to ask this since I couldn’t find any blogpost with similar subject, I have table where I want to replace certain text in one of the column with with another text. I know I can do this one by one by using Table.ReplaceValue() but is there a way I can do this more efficiently using another table which contains two columns, first contains the TextToReplace and 2nd Contains the Replacement text. I have about 50 texts to replace. I’ve been pulling hair trying to accomplish this using function that iterate but haven’t been successful so far. let me know if this is possible.

    chandan chauhan

    June 5, 2014 at 8:35 pm

    • I’m sure it is possible, but I need to think about it! If I can do it I will write it up as a blog post.

      Chris Webb

      June 6, 2014 at 10:16 am

    • Actually, I’ve just solved the problem so if you’d like me to send you the workbook then drop me an email. You can find my contact details at http://www.crossjoin.co.uk

      Chris Webb

      June 6, 2014 at 11:02 am

      • Thanks Chris, I’ve sent the email to info@.. email address

        chandan chauhan

        June 7, 2014 at 7:12 pm

  5. Chris,
    Regarding your statement above:
    “Interestingly there is no option to do a cross join using the Table.Join() and Table.NestedJoin() functions.”

    Unless I’m mistaken, it seems you can leave the “key” arguments for both tables in the functions as empty lists “{}” in order to perform a cross join. For the NestedJoin, it seems the function will only add the join column if the 2nd table uses the Table.PrefixColumns function to prefix the column names, even if they’re not the same to begin with, which seems odd.

    Hope that helps.

    logan

    August 26, 2014 at 2:12 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,202 other followers

%d bloggers like this: