Filter Excel Function CUBEVALUE Connected to Power BI

cubevalue cubeset formula

You might know that we can connect Excel to Power BI using “Analyze in Excel” feature.

If this is something new for you – check out following video (3 min)

By default, after clicking on “Analyze in Excel” you get Excel file with Pivot Table connected to Power BI.

There you can add rows, columns, slicers – everything as usual.


But what if Pivot Table is not what you need?

What if you have pixel perfect dashboard which you need to fill with CUBEVALUE formulas (or maybe you use Think Cell addin)?

And what if you want dropdown list of values in cell instead of slicers (or want filter to be generated via formula)?

Let’s check what we can do.

CUBEVALUE formulas

The easiest way to get CUBEVALUE formulas instead of Pivot Table is to convert Pivot Table to Formulas


That action deletes Pivot Table and replaces all its cells with functions CUBEMEMBER…


 

…and CUBEVALUE


As you may see, CUBEVALUE is referencing to a Slicer with countries and a cell with a CUBEMEMBER function.

Replace single value slicer with a cell

Everybody likes slicers, me too. However, in certain scenarios it is better to have a cell with value instead.

Why?

(more…)

Tracking expenses using Excel and MS Flow

tracking expenses

Do you track your personal/family expenses? If yes, I bet you use a specific software or service for this.

I personally think, there is nothing better than a simple Excel workbook for that task. It allows you to stay as flexible as you want, as simple or as complex as you want.

You can use Power Query to pull data from csv dumps from your bank accounts or currency exchange rates from your favourite financial web site, use Excel formulas to convert currency, Pivot Tables and charts to analyse and visualize data etc.

In this post you will see how you can enter data to your Excel spreadsheet with expenses from your mobile phone.

(more…)

Data type conversion in custom columns

data type conversion

You probably know that you can manually set data type for custom column created in Power Query with help of Table.AddColumn function (using 4th argument).

Rick de Groot has recently published a good post about this at Excelgorilla.com.

Just be sure you use right data type for the result of your calculation.

Once I faced strange Power Query behavior, as I thought, but later I understood that everything works fine (probably).

(more…)

Export PowerQuery query to CSV

Recently I found a PowerQuery gem, trick with Java/VB Script that allows to export data from Power Query to CSV without R / DAX Studio / SMS and Registration. However, related with risk. As everything else in our life.

Kudos to user Shi Yang from Stack Overflow who replied to How to write to DATA sources with Power Query?.

Shi proposes to use following code (extended with my comments)

Let
     // reference to a query you wish to export to CSV
    Source = ReferenceToYourTableOrQuery,
     // demote headers to have headers in resulting CSV
     // if you don't need headers, remove Table.DemoteHeaders
     Json = Text.FromBinary(Json.FromValue(Table.ToRows(Table.DemoteHeaders(Source)))),
     // trigger execution of script
  Export = Web.Page("
 var fso=new ActiveXObject('Scripting.FileSystemObject');
 var f1=fso.CreateTextFile('C:/Temp/test.csv',true);
 var arr=" & Json & ";
 f1.WriteLine(arr.join('\n'));
 f1.WriteBlankLines(1);
 f1.Close();
 ")
 in Export

All great, but this method doesn’t work with default settings of Internet Explorer.

(more…)

Transform Columns with List.Zip

This post continues series of articles about M Function List.Zip ( first post, second post).

Table.TransformColumns is another function that requires list of pairs if you want to transform several columns in Power Query


When we want to transform all fields we can simply use

Table.TransformColumns(#"Changed Type", {}, Text.Trim) 

// would be great to see same behaviour for Table.TransformColumnTypes

However, when we need to change only part of the table we have to generate list of pairs {column name, function}.

Text.Trim highlighted in last sample is a good example of transformation function.

In general, transformation function can contain any logic and can transform objects of any type.

(more…)

How List.Zip helps with Renaming Columns in Power Query

This is my second post about List.Zip. First one was about general usage of List.Zip, where I touched question of transforming column types in Power Query.

Another scenario where List.Zip can be used – renaming columns in Power Query.

When you rename columns manually, auto-generated function looks like

Table.RenameColumns( Source, 
     { {"Column1", "Col 1"}, 
     {"Column2", "Col 2"}, 
     {"Column3", "Col 3"}} )

As well as Table.TransformColumnTypes, it requires list of pairs {“Old Name” , “New Name” } for its second argument.

List.Zip helps to create list of pairs:

Table.RenameColumns( Source, List.Zip( { Table.ColumnNames( Source ), 
    { "Col 1", "Col 2", "Col 3" } } ) )

Result:


Dynamic column names

When we have, so called, “RenamingTable”, which contains two columns, first – with old name, second – with new name, we can use following pattern

Table.RenameColumns( TargetTable, 
    Table.ToColumns( Table.Transpose( RenamingTable ) ), 
    MissingField.Ignore )

You can read more detailed explanation in one of my previous posts.

Using List.Zip, we don’t need RenamingTable, as we can generate new names on the fly by using following pattern

(more…)

How to use List.Zip in Power Query

There is one useful Power Query M function – List.Zip, but with poor documentation on MSDN.

I hope, at some point, library of M functions will be available on Github like it is done for VBA. Power Query enthusiasts then would get a chance to contribute. E.g. from MSDN page of Workbook object we can go to Github and make a pull request for changes.

I plan 2-3 posts about application of List.Zip, this is the first one.

How does List.Zip work

Let’s start from “Help” in Power Query editor, it shows simple sample


Note: to get help on function – type name of function in formula bar and press Enter. Pay attention to register, M is case sensitive.

Having short documentation directly in power query editor is great idea! However, it is hard to show all scenarios with function and keep documentation short. In this particular case, it might be not obvious what happens when we have list of lists with more than 2 elements, or lists with different number of elements, or with more than two lists. (more…)

Power Query Cheat Sheet Update

Power Query cheat sheet repository

Just a short post today. I’ve updated my Power Query cheat sheet and created repository on Github so anyone can now contribute.

Good news for Russian-speaking readers. Шпаргалка теперь доступна на русском языке. Ура!

If you want to have this Cheat Sheet in any other language – just translate it and send me a pull request. Too difficult? Then send me an email with translated .docx attached.

Have several ideas for “PQ shortcuts” section, so stay tuned.

Go to Github and get your copy of Power Query cheat sheet.

Power Query cheat sheet repository

How to track refresh time in Power BI Desktop

Usually, I use Power Pivot and VBA in Excel to measure Power Query performance by comparing refresh time.

But, I suppose Power BI Desktop refresh process may be different, therefore would be nice to have something that would allow measure time between start and end of refresh.

Unfortunately, we do not have VBA in Power BI Desktop, nor can trigger and monitor refresh of Power BI Desktop from another application. So, we have only M and queries.

For the experiment I’ve created a new Power BI Desktop file, three queries in it, and put queries in order Start-Delay-End

Start

= DateTime.LocalNow()

Delay

= Function.InvokeAfter(()=> DateTime.LocalNow(), #duration(0,0,0,3))

End

= DateTime.LocalNow()

“Delay” must make a 3-seconds delay. You may read about Function.InvokeAfter in old good post from Chris Webb.

The idea is naive – hope that Power BI Desktop will execute queries in the same order as in the list of queries.

If so – query “Start” will load start time, Delay will make a pause, then “End” will load time of the end of refresh.

However, by default, Power BI Desktop loads tables in parallel, to optimize load time.

This property can be found in Options -> Current File ( Data Load ) -> Enable parallel loading of tables

This really works. I was receiving same time for Start and End tables while this property was enabled.

When I disabled it, I finally got desired difference between Start and End

Then I changed order of queries in the list of queries to check whether it impacts on execution order

Result shows that “Yes – order does matter“:

Of course, Power Query engine must be generating Execution plan when user press Refresh and then follows it. But in simple scenarios, when “Parallel loading of tables” is disabled, seems like Power BI Desktop follows order of queries from Query Editor.

I have no groups of queries, have no references between queries etc. It allowed me to check load time.

What about complex models

I tried to use same technique in more complex file – with groups of queries (but with no references).

I created query “Start” and placed it into the first group.

Similar for “End” query – but in the last group.

Result seems correct

Conclusion

Above is, of course, not a serious solution. Mainly, because you won’t want to disable parallel loading of tables, and won’t rely on order or queries.

Nevertheless, it would be good to have total refresh time directly in the model. It would allow to monitor refresh time of growing datasets.

More sophisticated way of query engine processing analysis is hidden in diagnostics of trace logs. You may read about this in several posts from Chris Webb here, here, and here.