Chris Webb's BI Blog

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

Archive for the ‘Power Query’ Category

Web Services And POST Requests In Power Query

with 5 comments

In my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function generates a GET request by default, you can make it generate a POST request by specifying the Content option. Since this is a useful thing to be able to do I thought I’d put together a detailed example of how this works.

For my example I’m going to use the Bing Maps Elevations API, which allows you to get the elevation in metres for a set of geographic locations. You can read the documentation here:

http://msdn.microsoft.com/en-us/library/jj158961.aspx

As it says at the bottom of the page, if you have a large number of locations to pass to the web service you can do so by passing them as a comma delimited list of latitudes and longitudes using a POST request. Here’s the code for a Power Query query that generates a list of latitudes and longitudes that stretches across the UK from North Wales in the west to the Wash in the east and finds the elevation for each point:

let

    //Insert your Bing Maps API key here

    BingMapsKey = "xxxx",

    //Latitude

    Latitude = 53,

    //Generate a list of longitudes

    Longitudes = List.Numbers(-4.5,66,0.1),

    //Generate a list of latitudes and longitudes

    PointList = List.Transform(Longitudes, 

        each  Number.ToText(Latitude) & "," & Number.ToText(_)),

    //Turn this list to comma delimited text

    PointListText = Text.Combine(PointList,","),

    //Add the text "points=" to the beginning

    PostContents = "points=" & PointListText,

    //Call the Elevations web service

    GetElevations = Web.Contents(

                    "http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,

                    [Content=Text.ToBinary(PostContents)]),

    //Treat the result as a JSON document

    ImportedJSON = Json.Document(GetElevations),

    //Navigate to the elevations data

    resourceSets = ImportedJSON[resourceSets],

    resourceSets1 = resourceSets{0},

    resources = resourceSets1[resources],

    resources1 = resources{0},

    elevations = resources1[elevations],

    //Turn the elevations data into a table

    TableFromList = Table.FromList(elevations, Splitter.SplitByNothing(), 

                                null, null, ExtraValues.Error),

    //Rename the column containing the elevations

    RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Elevations"}}),

    //Add a column containing the latitude

    InsertedCustom = Table.AddColumn(RenamedColumns, "Latitude", each Latitude),

    //Add an index column

    InsertedIndex = Table.AddIndexColumn(InsertedCustom,"Index"),

    //Use the index column to find the longitude for the current row

    InsertedCustom1 = Table.AddColumn(InsertedIndex, "Longitude", each Longitudes{[Index]}),

    //Remove the index column

    RemovedColumns = Table.RemoveColumns(InsertedCustom1,{"Index"}),

    //Set all columns to data type number

    ChangedType = Table.TransformColumnTypes(RemovedColumns,

                    {{"Elevations", type number}, {"Latitude", type number}, 

                    {"Longitude", type number}})

in

    ChangedType

 

It’s quite easy to edit the code so that it generates a list of latitudes and longitudes across the country of your choice…

Two things to point out:

  • To get this to work you need to insert a Bing Maps API key in the first step where indicated. If you don’t have one, you can get your own at https://www.bingmapsportal.com
  • The important step is GetElevations. The code is:

    Web.Contents(

    "http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,

    [Content=Text.ToBinary(PostContents)]),

    You can see here how the list of locations is passed to the Web.Contents() function (documentation here) via the Content field; notice also that I’ve had to use Text.ToBinary() on the text that I’m passing in.

Here’s the output in Power Map:

image

You can download the sample workbook here.

Written by Chris Webb

April 19, 2014 at 9:00 am

Working with Web Services in Power Query

with 13 comments

One of many cool things about Power Query is the way that it allows you to retrieve data from web services and load it into Excel. While this is a subject that lots of people (including me) have already blogged about, the Web.Contents() function – which is the M function that you’ll need to use to call a web service in your query – has gained some new, useful functionality over the last few releases that isn’t fully documented anywhere and which is important to know about.

For this post I’ll use a real-life example of a web service that I’ve been working with recently. As you may know, I’m one of the organisers of SQLBits and a few weeks ago I was given the task of building a Power BI solution to monitor registrations. SQLBits uses RegOnline to handle registrations and they have an API that allows developers to access registration data for events. I’ve been using this API in Power Query.

To take a simple example that illustrates the new functionality in Web.Contents(), take a look at the documentation for RegOnline’s GetEvents method:
http://developer.regonline.com/getevents/

When calling this method in Power Query using Web.Contents() there are two things I need to do:

  1. Pass two query parameters to it, called filter and orderBy – although I only need to pass empty values to these parameters
  2. Handle authentication by passing an API token through the HTTP header

Here’s one way of using Web.Contents() to do this:

Web.Contents(

  "https://www.regonline.com/api/default.asmx/GetEvents", 

  [

   Query=[ #"filter"="", #"orderBy"=""], 

   Headers=[#"APIToken" = "insertAPITokenHere"]

  ])

 

In the example above, the second parameter to Web.Contents() is a record (so it has to be enclosed in square brackets) containing two options fields, Query and Headers. The Query field is itself a record, with one field for each of the two query parameters I’m passing. The Headers field is another record, with one field for each custom HTTP header I need (see this post on the Power Query forum for more details). The RegOnline API specifies that for the purposes of authentication a header is needed called APIToken which contains the API token, so the Headers option here contains one field for that header.

The problem with handling authentication like this is that I need to hard-code the RegOnline API token in the code for my Power Query query, which is not exactly secure. Therefore instead of doing this I use the ApiKeyName option to tell Power Query the name of the header that should contain the RegOnline API token:

Web.Contents(

 "https://www.regonline.com/api/default.asmx/GetEvents", 

 [

  Query=[ #"filter"="", #"orderBy"=""], 

  ApiKeyName="APIToken"

 ])

 

This way, when the Power Query query is executed for the first time, you get prompted to tell Power Query how to authenticate the GetEvents method and in the dialog you have to choose Web API and enter the API token in the Key box, as shown below:

image

After this the API token gets stored separately from the query in Power Query’s own secure credentials store and you don’t need to enter it again; this also means that the API token will not travel with the workbook if you email it to someone else, for example.

One last thing to mention (and this is something that deserves a separate blog post really) is that while Web.Contents() generates a GET request by default, you can make it generate a POST request by specifying the Content option. You can find details on how to do this on this thread started by Lee Hawthorn on the old Data Explorer forum.

Written by Chris Webb

March 26, 2014 at 2:35 pm

Posted in Power Query

Pretty Patterns With Power Query And Power Map

with one comment

Here’s something with no practical use whatsoever. Today, after I finished writing the first draft of the chapter on M of my upcoming Power Query book, I got thinking about how Power View and Power Map get all the attention because of all the eye-catching demos you can create with them. And then I thought – why bother spending time finding real data for these demos when you can generate artificial data in Power Query to create patterns? So I got to work…

As you probably know, you can create animated charts in Power Map so long as you have date-based data. I therefore created a function in Power Query to draw a circle as a series of points on a graph where each point is associated with a date; I also added data for height and colour for each point. Here’s the function definition:

let

    //declare function to draw a circle

    CircleFunction = (CircleRadius as number, StartDate as date, Reverse as logical) =>

let

    //set the radius

    radius = CircleRadius,

    //create a list of numbers from 0 to 359

    anglelist = List.Numbers(0, 359, 1),

    //function to convert degrees to radians

    radians = (a) => (a * 2 * Number.PI)/360,

    //create a list of 360 dates starting from the start date

    unordereddatelist = List.Dates(StartDate, 360,#duration(1,0,0,0)),

    //reverse the list of dates if the Reverse parameter is True

    datelist = if Reverse then List.Reverse(unordereddatelist) else unordereddatelist,

    //generate the list of points on the graph, one for each angle and date

    positionlist = List.Transform(anglelist, each 

                    {_, datelist{_}, Number.Cos(radians(_)) * radius, 

                    Number.Sin(radians(_)) * radius, Date.Month(datelist{_}), 

                    Number.Abs(Number.Cos(radians(_)))*10}),

    //convert the list of points to a table

    outputtable = Table.FromRows(positionlist, {"Angle", "Date", "x", "y", "Colour", "Size"}),

    //set data types

    ChangedType = Table.TransformColumnTypes(outputtable,

                    {{"Angle", type number}, {"Date", type date}, {"x", type number}, 

                     {"y", type number}, {"Colour", type number}, {"Size", type number}})

in

    ChangedType

in

    CircleFunction

 

I then created another Power Query query to call this function 30 times to create 30 circles with different radiuses:

let

    //generate a list of numbers from 0 to 29

    circlelist = {0..29},

    //generate a list of 30 dates starting on 1 January 2014

    datelist = List.Dates(#date(2014,1,1), 30,#duration(1,0,0,0)),

    //call the Circle() function 30 times

    tablelist = List.Transform(circlelist, each Circle(_+5, datelist{_}, Number.Mod(_,2)=0)),

    //combine the resulting tables into a single table

    positionlist = Table.Combine(tablelist)

in

    positionlist

 

And here’s the result of the query plotted on a map using Power Map:

Pretty, isn’t it? You can download the workbook with the Power Query query and the Power Map tour here.

Written by Chris Webb

March 20, 2014 at 11:34 pm

Posted in Power Map, Power Query

User-Defined Conditional Logic In M

with 4 comments

Here’s a short follow-up to my last post on conditional logic in M. After that post went live, Ehren Vox of the Power Query team made a good suggestion on Twitter: rather than hard-code the list of conditions and values inside the query, why not take those values from the Excel spreadsheet too? That way end-users can maintain the conditions and values themselves.

Here’s my Excel spreadsheet, now with two tables: one called Input, containing my input value, and one called CaseValues containing my conditions and return values.

image

And here’s my new query, a variation on the simple case statement query from my previous post, but this time using the values from the CaseValues table to drive the logic:

let

    //load input value table from worksheet

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

    //get input value from that table

    InputValue = Source{0}[Input],

    //load case values from worksheet as a table

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

    //turn that table into a list and append the else condition to the end

    CaseValues = List.Combine({Table.ToRows(CaseTable),{{InputValue, "Else condition"}}}),

    //look for the input value in the CaseValues list and return the value associated with it

    SimpleCase = List.First(List.Select(CaseValues, each _{0}=InputValue)){1}

in

    SimpleCase

The output here, once again, is the text value “Five”. Two interesting things to notice here:

  • I used the Table.ToRows() function to turn the table containing my case values into a list of lists
  • I used List.Combine() to append the else condition (a list containing two values, the input value and the text “Else condition”) onto the end of the list returned by Table.ToRows()

I’ve added this example to my original demo workbook, which can be downloaded here.

Written by Chris Webb

March 14, 2014 at 11:38 pm

Posted in Power Query

Conditional logic in Power Query

with 8 comments

Writing a simple if statement in Power Query’s M expression language is straightforward. Using an Excel table called Input that contains a single value as the starting point:

image

The following query shows how to use an if … then … else statement to test whether the value from the table is equal to 5:

let

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

    InputValue = Source{0}[Input],

    IfStatement = if InputValue=5 

                    then "The number is five" 

                    else "The number is not five"

in

    IfStatement

What about more complex conditional logic? The M language doesn’t include anything like a case statement, but it is possible to write the equivalent of one quite easily.

Here’s an example of a simple case statement:

let

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

    InputValue = Source{0}[Input],

    CaseValues = {

                    {1, "First"},

                    {2, "Second"},

                    {3, "Third"},

                    {4, "Fourth"},

                    {5, "Fifth"},

                    {InputValue, "Else condition"}

                },

    SimpleCase = List.First(List.Select(CaseValues, each _{0}=InputValue)){1}

in

    SimpleCase

This works as follows:

  • The CaseValues step defines a list containing six items, each of which is itself a list containing a number and some text. The number is the value to compare to the input value, and the text is what will be returned if the number does match the input value.
  • The last item in the CaseValues list contains the input value, so this will be returned where the input value matches none of the preceding values
  • The SimpleCase step uses List.Select() to filter the list in CaseValues so that only the items in the list where the input value matches the number in the list.
  • Since List.Select itself returns a list, this list is then passed to List.First() to get the first item in the list returned by List.Select (there should only be one item in the list in this particular query), and then {1} returns the text from that item. This is the output of the query.

You can write a searched case expression in a very similar way, by declaring functions that return boolean values instead of using numbers as follows:

let

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

    InputValue = Source{0}[Input],

    CaseValues = {

    { (x)=>x<10, "Less than 10"},

    { (x)=>x<20, "Less than 20"},

    { (x)=>x<30, "Less than 30"},

    { (x)=>x<40, "Less than 40"},

    { (x)=>x<50, "Less than 50"},

    { (x)=>true, "Else condition"}

    },

    SimpleCase = List.First(List.Select(CaseValues, each _{0}(InputValue))){1}

in

    SimpleCase

 

In this query the CaseValues step contains a list of lists, where each item in the list consists of list containing a function and a text value. List.Select calls each function and only returns the items where the function returns true, and finally the text from the first item that List.Select returns is the output of the query.

You can download the sample workbook here.

Written by Chris Webb

March 10, 2014 at 9:00 am

Posted in Power Query

Allocation in Power Query

with 12 comments

Now that the brave new world of self-service BI is upon us, old-school corporate BI types like me need to sharpen our Excel skills – and anyone learning Excel will, sooner or later, end up on Bill Jelen (aka Mr Excel)’s site. I found his latest podcast on splitting the value of a contract over N months particularly interesting not only because I had to deal with a similar problem with a client only a few weeks ago but also because the problem of allocation in Power Query is something I’ve been meaning to blog about for a while. In this post I’m going to take the same data that Bill and Mike Girvin (whose book on Excel array formulas I got for Christmas!) used in the podcast and show how to achieve the same results they did but in Power Query and Power Pivot.

My starting point is an Excel sheet with two tables named Contract and Month, shown below:

image

It’s not exactly the same layout as in the podcast but that’s deliberate – I want to keep my source data and my output (which could be a PivotTable, cube formulas or a Power View sheet) separate.

Next, I import the Month table into Power Query using the From Table button and then click on the Add Index button to add an index column, so that the query output is as follows:

image

I don’t need to load this anywhere though, even though I’m going to use its output in the next query, so I leave both of the boxes in the Load Settings section of the Query Editor unchecked and go back to the worksheet:

image

Next, I import the Contract table and add an index column in the same way:

image

I can now add a custom column to calculate the monthly amount by dividing Contract Amount by Months In Contract:

image

Now comes the interesting bit. I insert another custom column and this time the M expression to paste into the dialog is:

Table.FirstN(Month, [Months In Contract])

In each row this column contains a table containing the first N rows of the Month table, where N is the value from the [Months In Contract] column. The output is this:

image

I then just need to click on the expand icon next to the column header of the Custom column to repeat each contract row for all the months it applies to, rename the columns and set the column types appropriately, and I’m ready to load into the Excel Data Model:

image

Here’s the full M code for both queries:

--Month Query

let

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

    InsertedIndex = Table.AddIndexColumn(Source,"Index"),

    ReorderedColumns = Table.ReorderColumns(InsertedIndex,{"Index", "Month"})

in

    ReorderedColumns


--Contract Query

let

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

    InsertedIndex = Table.AddIndexColumn(Source,"Index"),

    RenamedColumns = Table.RenameColumns(InsertedIndex,{{"Index", "ContractID"}}),

    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"ContractID",

                            "Months In Contract", "Contract Amount"}),

    InsertedCustom = Table.AddColumn(ReorderedColumns, "Allocated Amount",

                            each [Contract Amount]/[Months In Contract]),

    InsertedCustom1 = Table.AddColumn(InsertedCustom, "Custom",

                            each Table.FirstN(Month, [Months In Contract])),

    #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom1, "Custom",

                            {"Index", "Month"}, {"Custom.Index", "Custom.Month"}),

    RenamedColumns1 = Table.RenameColumns(#"Expand Custom",{{"Custom.Index", "MonthID"},

                            {"Custom.Month", "Month"}}),

    ChangedType = Table.TransformColumnTypes(RenamedColumns1,{{"Allocated Amount", type number},

                            {"Contract Amount", type number}, {"MonthID", type number},

                            {"Months In Contract", type number}, {"ContractID", type number}})

in

    ChangedType

 

Last of all, I need to go into the Power Pivot window and do two things:

  • Use the Sort By Column functionality to sort my Month column by MonthID
  • Format my Allocated Amount column using a dollar sign

And I’m ready! I can now create a PivotTable containing my allocated values:

image

Maybe it’s a little bit more long-winded than Bill or Mike’s examples but I don’t think it’s any more complex. And of course, now the data is in the Excel Data Model I have a lot more flexibility on how to present the data. For example I can use Power View with no remodelling or formula changes necessary:

image

One last point: I know it’s good practise to use a separate Date table with Power Pivot. I didn’t do so here because I wanted to keep as close to the original example as possible. And because I’m lazy.

You can download the demo workbook here.

Written by Chris Webb

February 24, 2014 at 9:10 pm

Posted in Power Query

Power Query Session From 24HOP Now Available To Watch

with 4 comments

The recording of my session, “Power Query: Beyond The Basics”, from yesterday’s 24 Hours of PASS event is already available to watch here:
http://bit.ly/1fUGzAr

It’s a brief introduction to the M language that’s used in Power Query and shows some of the things you can do with it. You can download the slides and the demo material (a lot of which I didn’t have time to cover) from here:
http://sdrv.ms/1gOXOXk

All of the other sessions from the event can be found here:
http://www.sqlpass.org/bac/2014/Sessions/SneakPeeks.aspx

Written by Chris Webb

February 6, 2014 at 10:47 am

Posted in Power Query

Loading Power Query M Code From Text Files

with 14 comments

One of the things that has intrigued me in the Power Query docs for a while now is the Expression.Evaluate() function, which takes some text and evaluates it as a Power Query expression (rather like good old StrToSet() in MDX). I can think of lots of fun things you can do with this, but here’s one very cool example: it allows you to store the M code for your Power Query query in a text file outside your Excel workbook.

Here’s an example. Consider the following Excel workbook, which has a table named FruitSales in it:

image

I can use this table as a source for the following simple Power Query query as follows:

let

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

    GroupedRows = Table.Group(Source, {}, {{"Sum of Sales", each List.Sum([Sales]), type number}})

in

    GroupedRows

 

Here’s the output:

image

No surprises so far. Next, I copy the code for the Power Query query above and paste it into a text file; in my case I’ve saved my file at C:\PowerQueryQueries\DynamicQuery.txt

image

Back in Excel, I can now load the code stored in this text file in a new query using the following code:

let

    //Load M code from text file

    Source = Text.FromBinary(File.Contents("C:\PowerQueryQueries\DynamicQuery.txt")),

    //Evaluate the code from the file as an M expression

    EvaluatedExpression = Expression.Evaluate(Source, #shared)    

in

    EvaluatedExpression 

 

The end result is exactly the same as in the previous example, except that in this case the code to read the data from the table and to aggregate it is loaded from the text file and is not stored inside the workbook.

The Source step here is fairly straightforward – it just loads text from a file into Power Query. It’s the next step where the magic takes place: as I said, Expression.Evaluate() takes the text and evaluates it as an expression, but it’s the second parameter which defines the environment that the expression evaluates in that seems to be the key to making this work. The chapter on “Sections” in the Power Query Formula Language specification document has a little bit more explanation of what environments are but I have to admit I’m not 100% clear on how all this ties in to M as it is implemented in Power Query today.

For those of you who can’t afford a Power BI subscription, this technique allows you to share queries between multiple workbooks without a Power BI site. In fact it has some advantages over sharing a query in Power BI because it always reads the definition of the query from the file, and so it will always use the latest version of your query – in Power BI, when you use a shared query you take a copy of the query and it is not updated even if the original author shares a newer version.

Written by Chris Webb

February 4, 2014 at 3:32 pm

Posted in Power Query

Comparing Columns In Power Query

with 12 comments

A 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 rather than whole tables. Of course this prompted me to investigate how different types of comparison might be done – and here’s the blog post with the results of the investigation.

Consider the following two single-column tables in an Excel worksheet:

image

Which items are present in one column and not in the other? Which are present in both? The easiest way to answer these questions is to take each table and turn it into a List object (using Table.ToList() ); once you’ve done that you’ll find there are loads of really useful functions for this type of thing. Here’s a query that compares the values in each column:

let

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

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

    SourceList = Table.ToList(Source),

    TargetList = Table.ToList(Target),

    InSourceNotTarget = List.Difference(SourceList, TargetList),

    InTargetNotSource = List.Difference(TargetList, SourceList),

    InTargetAndSource = List.Intersect({SourceList, TargetList}),

    CombineWithComma = Combiner.CombineTextByDelimiter(", "),

    ResultsTable = Table.FromRows(

             {

         {"In Source but not in Target", CombineWithComma(InSourceNotTarget)},

             {"In Target but not in Source", CombineWithComma(InTargetNotSource)},

             {"In both Target and Source", CombineWithComma(InTargetAndSource)}

             },

             {"Comparison Type", "ListResult"}

             )

 

in

    ResultsTable

 

Here’s the output:

image

Fairly self-explanatory, I think. List.Difference() finds the items that are in one list and not another: List.Intersect() finds items that are in both. In fact it’s probably more interesting to look at how I’ve generated the output. Table.FromRows() returns a manually constructed table. The CombineWithComma step uses Combine.CombineTextByDelimiter() to return a function that turns all of the items in a list into a single, comma-delimited piece of text, and I then use that function inside each row of the table I’m returning to get a readable version of what List.Difference() and List.Intersect() return.

Rather than looking at the distinct values in each column, though, you might want to do a row-by-row comparison. Here’s another query that does that:

let

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

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

    SourceList = Table.ToList(Source),

    TargetList = Table.ToList(Target),

    PositionList = List.Positions(SourceList),

    RowComparison = List.Transform(

                      PositionList, 

                      each 

                       if 

                        SourceList{_}=TargetList{_} 

                       then 

                        {_+1, "No Change",  SourceList{_} } 

                       else 

                        {_+1, "Change", "Source: " & SourceList{_} & 

                        ", Target: " & TargetList{_} }),

    ResultsTable = Table.FromRows(RowComparison, {"Row Number", "Changed?", "Comparison"})

 

in

    ResultsTable

Here’s the output:

image

Again I’m turning each table into a list, and then I’m using List.Positions() to generate a list of integer values from 0 to 9 representing the index of each item in the source list, then using List.Transform() to iterate over each item in this list and compare the values at the given index in the source and target list.

Frankly, an even easier way of doing this might have been to import both tables in separate queries, add an index column to both of them using the Insert Index Column button, then join the two tables together on the index column using the Merge button and then finally create some custom columns to do the comparison. This is certainly how any end-user would do it, but the resulting code is a bit less elegant I didn’t learn anything interesting about M from doing it that way. I’ve left the example in the demo workbook, which you can download here.

PS Even if you have voted for me already in the Power BI competition, please vote for me again (you can vote once every 24 hours)! Here’s the link:

CLICK HERE TO VOTE FOR ME IN THE POWER BI COMPETITION! EVEN IF YOU’VE VOTED ALREADY YOU CAN DO SO AGAIN!

Written by Chris Webb

January 27, 2014 at 10:02 pm

Posted in Power Query

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

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers