Chris Webb's BI Blog

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

Comparing Tables In Power Query

with 5 comments

An interesting Power Query nugget for you: you can compare two tables using the Value.Equals() function. For example, take the following worksheet with five Excel tables on it:

image

The following Power Query query compares Table 1 with each of the other four tables and tells me whether they are identical or not:

let

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

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

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

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

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

    Output = Table.FromRows(

                {

                {"Table2", Value.Equals(Table1, Table2)},

                {"Table3", Value.Equals(Table1, Table3)},

                {"Table4", Value.Equals(Table1, Table4)},

                {"Table5", Value.Equals(Table1, Table5)}

                },

                {"Compared Table", "Is Equal To Table 1"}

        )

in

    Output

 

All the code here is doing is loading the five Excel tables and then outputting a single table that shows the result of Value.Equals() when you compare the first table with the other four. Here’s the output:

image

I’ve tested this on tables sourced from SQL Server and quickly ran into a bug that crashed Power Query, but it seems as though Value.Equals() returns True when you pass it two identical tables and False when you pass it a table and a view which is just a SELECT * from that table. I wonder if there’s some extra metadata that allows Power Query to tell the difference between a table and a view? More research needed I think.

Overall this seems quite a handy trick to know about. This post has barely scratched the surface of what you can do with Value.Equals() though – you can compare any two values, not just tables, and you can specify your own function to do the comparison. As with so much of Power Query there’s a lot to learn… but that’s what makes it so fun!

You can download the sample workbook here.

Written by Chris Webb

January 11, 2014 at 3:13 pm

Posted in Power Query

5 Responses

Subscribe to comments with RSS.

  1. Hat dies auf MS Excel | Power Pivot | DAX rebloggt.

    selfservicebi

    January 11, 2014 at 4:46 pm

    • Hi Chris,

      in the example you build a table with the results “from scratch”. Is there the possibility to have a comparison with “joined” data in a merged query? Will say to have the comparison in a column at the end of a merged query to determine differences between columns. I didn’t manage to write a kind of if…then…else up to now…

      Best regs
      Patrick

      Patrick

      January 13, 2014 at 2:09 pm

      • You know, I have an idea how to do this but it needs testing. If my idea works I’ll write a blog post explaining it!

        Chris Webb

        January 13, 2014 at 3:45 pm

  2. […] few weeks ago I wrote a post about comparing the contents of entire tables in Power Query, and a question came up in the comments about how you might go about comparing values in columns […]

  3. […] few weeks ago I wrote a post about comparing the contents of entire tables in Power Query, and a question came up in the comments about how you might go about comparing values in columns […]


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 2,868 other followers

%d bloggers like this: