Posts in Category ‘Power Query’

Fast and easy way to reference previous or next rows in Power Query or Power BI

When you search the web for solutions to reference previous or next rows in the query editor of Power BI or Power Query, you will find many solutions that base on an added index-column. But the problem with these solutions on large tables is that performance will range between slow and extremely slow. In this post I’ll show a faster method with function Table.ReferenceDifferentRow .

Basic mechanism

This new mechanism doesn’t use an index that is either used to merge the table with itself or to be referenced as a new row index. Instead, I “simply” add one table next to the other. To retrieve the previous row from a table, I reference the original table, delete its last row and add a blank row on top. That will “shift” the first row to the second row. Then I “put” this table just right to the original table, without referencing a key or applying any sort of logic. This will speed up the process considerably:

Shift table to reference different rows

The key of this method is the Table.FromColumns-function: It creates a table from a list of columns in the order of the columns in the list. So I just have to find a way to turn 2 tables into 1 list of columns:

Table.ToColumns(OriginalTable) & Table.ToColumns(ShiftedTable)

will do this job. Table.ToColumns will turn a table into a list of columns and the ampersand (“&”) will concatenate the lists from both tables.

The function

I’ve included this basic mechanism into a handy function with some bells and whistles: “Table.ReferenceDifferentRow”

 

How it works

  1. The only mandatory parameter is your table and then it will return a table with the previous rows values of all columns. So Table.ReferenceDifferentRow(MyTable) will return the result from above.
  2. The default-value for this parameter is set to -1 to return the previous row if you omit it. If you want the values from the next row instead, fill in 1. 2 will return the overnext and -2 the pre-previous row. This is what Table.ReferenceDifferentRow(MyTable, -2) returns:

    -2 will return the pre-previous row

     

  3. You probably just need one or a few columns/fields from the previous row: In the 3rd parameter you can enter a list of column names to be returned:   Table.ReferenceDifferentRow(MyTable, null, {"Value"}):

    Select specific columns

     

  4. Quite often the previous values shall only be returned within a group of rows. (That’s when you use [MyColumn] = EARLIER([MyColumn]) in DAX). You can enter a list of group-columns in the 4th parameter of this function: Table.ReferenceDifferentRow(MyTable, null, null, {"Product"})

    Group by columns

  5. By default, the suffix “.Prev” will be added to the new column names. Use the 5th parameter to change if needed. In this example, I reference the row below using “1” for the 2nd parameter: Table.ReferenceDifferentRow(MyTable, 1, null, null, "Next")

    Changing suffix and referencing next row

  6. If performance is still too bad, you can try to buffer the input table. Any value in the 6th parameter will do that for you (although I haven’t seen a performance improvement for my test cases).

 

Why not use DAX?

Referencing a previous row in DAX is still faster than my method above. So if you can do it in DAX, go ahead (create an index in the query editor and use LOOKUPVALUE like shown here: https://powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/. ) My function is for cases where you have to stay in M.

Enjoy & stay queryious 😉

Efficiently rename columns with a function in Power BI and Power Query

Edit 2018-July-05: As it turns out I’ve missed the native function that exists with exact the same functionality: Table.TransformColumnNames Thanks to Tristan Malherbe for pointing this out 🙂

But anyway: If you want to understand what it does and how you could have written it by yourself – read along:

The standard function to rename columns in the query editor of Power BI or Power Query is Table.RenameColumns. It takes a nested list of old and new column names as its second argument. But I often come across scenarios where I would like the new column names to be created dynamically. Therefore I’ve created a function (Table.RenameColumnsWithFunction) that takes a text transformation function as its argument instead of a hardcoded list of values:

Problem

Say you’re importing tables where the table name is part of each column name like so:

Old column names for Table.RenameColumnsWithFunction

 

 

But you only want to maintain everything after the TABLENAME.:

New column names for Table.RenameColumnsWithFunction

 

 

 

One way would be to replace “TABLENAME.” by nothing (“”):

Table.RenameColumnsWithFunction

This function allows you to pass the function as a second argument instead of a hardcoded list of new names like so:

Table.RenameColumnsWithFunction

 

 

 

 

 

As you can use any appropriate function, an alternative could also be to use is like this for example:

Table.RenameColumnsWithFunction( MyTable, each Text.BeforeDelimiter(_, ".")

So the underscore (_) stands for each column name of the table that you’re passing in as the 1st parameter of the function.

Function Code

If you don’t know how to handle custom functions, check out this video: https://www.youtube.com/watch?v=6TQN6KPG74Q

How it works

Details to row numbers:

  • 4: The old column names are retrieved and returned as a list
  • 5: Each item from that list will be transformed with the transformation function that has been passed in as the 2nd parameter
  • 6: Both lists are zipped to be in the required shape for the native function “Table.RenameColumns”
  • 7: The native function “Table.RenameColumns” is applied to perform the desired renaming operation
  • 10 ff: This is just function documentation that will appear when you address this function in the program

Hope this will help you one day, so

enjoy & stay queryious 😉

Automatically expand all fields from a JSON document in Power BI and Power Query

If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what’s in there. The following function has you covered for this task. It returns a table with all values in column “Value” and additional columns describing where that value came from in a hierarchical form, no matter how deep the nesting goes:

Example

See below what this function does for the following JSON :

Automatically expand JSON: Sample JSON

{"A":"A Record", "B":["ListItem1", {"C":"A nested Record", "D":"Another nested Record"}], "E":{"F":["NestedListItem1","NestedListItem2","NestedListItem3"]}}

Automatically expand JSON: Result

 

  • Value: The respective values
  • Level: Main levels.
  • Sort: Sort column with hierarch to display in report
  • SortBy: Sort your “Sort”-column in the data model by this column: It will be filled up with “0” according to the maximum value within the respective position. This effectively allows sorting by number, although it is a text-field.
  • Name-columns: They display the hierarchical location of the value with regards to JSONs record field names.

The Function

 

How the code works

  • The main elements in JSON are records and lists and they can appear in many different combinations. So to handle them equally, I often convert them into a common type so that they can be further processed equally (row 6, 27 and 32).
  • I use List.Generate (row 10-42) to repetitively check whether the returned values are further expandable or not. If they are not expandable, they go into “Finished” (row 25) and if they need further expanding, they land in “Unfinished” (row 26) and will be expanded further.
  • Row 48-56 contain a dynamic padding that will convert the Sort-entry “2.1” into “02.1” in column “SortBy”, if there is also a “10.x” in the column. This will make sure that the (alphabetical) sort returns “2.1” before “10.1”. (And if there is a 100.x it will convert to “002.1” and so on…)

I’m pretty sure that this code can be further improved, as it has been evolved for quite some time and I didn’t re-engineer it. So if you’re up for it, please don’t hesitate to post an improved version!

For large JSONs, the table can get very long and it could be beneficial to view it in a more compact form (actually a form that would provide tables for a relational model). I will show this in an upcoming article, so if you’re interested in it, make sure to subscribe to my blog not to miss it.

Enjoy & stay queryious 😉

Easy POST requests with Power BI and Power Query using Json.FromValue

The function Json.FromValue provides a super-easy way to create POST-calls to web services that require JSON format in their body parameters.

Background

If you want to make a POST request to a web service through Power Query, you have to add the relevant data in the “Content”-parameter of the query (see Chris Webb’s article here for example). This is a pretty nifty method that transforms the default GET-method to a POST automatically. The content of that parameter has to be passed in as a binary and therefore the Text.ToBinary function can be used. This will serve well in many cases, but if your service requires a JSON record and you happen to have that record somewhere in your query already, transforming it to text can get pretty cumbersome and is actually not necessary:

Problem

Say you want to use Microsoft’s Translate API to translate values from a column to a different language. This API lets you pass in multiple strings into one call if you pass them in as a JSON array of records. So instead of transforming them all into a long string of text that represents the JSON-syntax, you can simply let come

Json.FromValue to the rescue

List.Transform ( YourColumn, each [Text=_] )

will transform “YourColumn” into a list of records that represents the required JSON-array.

The function Json.FromValue (which hides itself in the Text-category of M-functions) takes actually in ANY format from Power Query and transforms it into a binary JSON-format. Pass this into the Content-parameter and you’re good to go.

Note: There is a little flaw with the current version of the MS Translate API and in my next blogpost I will show how to tackle it.

Enjoy & stay queryious 😉

Import data from multiple SharePoint lists at once in Power BI and Power Query

This is a quick walkthrough on how you can easily import multiple SharePoint lists at once, just like the import from folder method.

Start as usual

You start your import like this:

Pass the URL to the folder where your lists are located:

In the next step you would normally choose all the multiple SharePoint lists you want to import:

Read more

Automatically create function record for Expression.Evaluate in Power BI and Power Query

Some time ago I wrote a blogpost on how to create a function library in Power BI or Power Query (http://www.thebiccountant.com/2017/08/27/how-to-create-and-use-r-function-library-in-power-bi/). There I also presented a way to pull that function code automatically from GitHub.

Problem

In that code I used the function Expression.Evaluate to execute the imported text and create functions from it. The inbuilt functions that I’ve used in that code have to be passed as an environment record at the end of the expression. I’ve used #shared for it, as this returns a record with all native M-functions and is quick and easy to write (if environments are new to you, check out this series: https://ssbi-blog.de/the-environment-concept-in-m-for-power-query-and-power-bi-desktop/ ). But as it turns out, this can cause problems when publishing to the service unfortunately (https://social.technet.microsoft.com/Forums/ie/en-US/208b9365-91e9-4802-b737-de00bf027e2a/alternative-calling-function-with-text-string?forum=powerquery – please leave a vote if you would like to use #shared in the service as well).

Solution

Read more