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

Permutations table in Power BI or Power Query in Excel

Today my husband came with a special task to me: He had to re-wire an old instrument on board of our sailing boat and “lost” the original matching instructions. There were 4 cables to go into 4 different holes, which left him with 24 possible combinations. So he needed a permutations table with all possible combinations that he could print out and tick each combination that he would try subsequently:

To be honest, he was quicker in creating the permutations table manually in Excel than me in Power Query, as it took me a while to develop an easy enough algorithm. But the technique I came out with at the end is so typical “Power Query”-ish, that I’m going to share it here:

The Video

The Function

Here comes the function code:

Read more