Posts in Category ‘Excel’

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 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

Stretching and Compressing Time Series with Power Query and Power BI

The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:

The Challenge

Stretching or compressing a forecast so that the proportion of the original series will be maintained:

The Function

This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:

Read more

Migrate a Power Query or Power BI file to a local SSAS instance

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher:

The steps:

  1. Import the Excel file in Power BI Desktop, save and close the pbix-file
  2. Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
  3. Open that model with Visual Studio (this will actually create a download that holds the VS-file)
  4. Open that file in Visual Studio, load the data, build and change the deployment target from Azure to you local SSAS-database before deploying.

See how it goes:

Warning: There are some limitations for the M-functionalities in SSAS (see here for example: General Overview by Microsoft or Use your own SQL … by Chris Webb), so you might want to give it a thorough test before rolling out. There are missing a lot of data sources currently, like web-queries for example who will hopefully soon be added as well.

This method has been described by Soheil Bakhshi here before: http://biinsight.com/import-power-bi-desktop-model-to-ssas-tabular-2017-using-azure-analysis-services/ 

Enjoy & stay queryious 😉

A generic SWITCH-function for the query editor in Power BI and Power Query

Although you can easily replicate the DAX SWITCH-function via list-, table- or record functions in M, I thought it would be convenient for many newbies to have a comfortable M-SWITCH-function that uses (almost) the same syntax than its DAX-equivalent:

SWITCH (
[Month],
    1, “January”,
    2, “February”,
    3, “March”,
    4, “April”,
    5, “May”,
    6, “June”,
    7, “July”,
    8, “August”,
    9, “September”,
    10, “October”,
    11, “November”,
    12, “December”,
    “Unknown month number”
)
DAX Formatter by SQLBI

The DAX-SWITCH-function will retrieve the content of its first argument (expression) ([Month]) and check it against he first parameters of the following pairs (value). If there is a match, the second parameter of the pairs (result, here: month name) is returned and if there is no match, “Unknown month number” will be returned.

How it works

The syntax for the M-function looks like so:

Read more