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

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

Publish Excel file to PowerBI using VBA

Recently Teo Lachev released a very short but important post about Automation Excel to PowerBI publishing.

In a nutshell: now we have a VBA method PublishToPBI, which allows to publish workbooks to PowerBI.

This is quite interesting scenario for Excel-based Self-Service BI solutions.

I played around with this method, and share with you results of my experiments.

Excel VBA PublishToPBI method documentation

Unfortunately, there is no official documentation yet. At least in the list of Workbook Methods.

Below is my modest attempt.

Purpose

Publish workbook to PowerBI as a report or dataset.

Requirements

  • workbook must be saved in xlsx or xlsm format.
  • User with PowerBI license must be signed-in in Excel.
  • for publishing as a dataset – workbook must have Excel Table or Data Model.

Syntax

expression.PublishToPBI( PublishType, nameConflict, bstrGroupName )

expression a variable that represents a Workbook object.

Parameters

Name Required/Optional Data Type / Description
PublishType Optional Enum XlPublishToPBIPublishType

msoPBIExport = 0, publish workbook as a dataset. Workbook must contain Table Data (range formatted as a Table) or Data Model (aka PowerPivot model).

msoPBIUpload = 1 (default) publish workbook as a report, which can be consumed in Excel Online directly in PowerBI.

nameConflict Optional Enum XlPublishToPBINameConflictAction

msoPBIAbort = 1 (default), workbook will not be loaded to PowerBI if there already exists workbook with same full name (including extension)

msoPBIIgnore = 0, ignores existence of report / dataset and creates new one on Upload/Export.

msoPBIOverwrite = 2, overwrites existing report / dataset with same full name of workbook. Note: method raises an error if find more than one report / dataset with same full name (e.g. if msoPBIIgnore was used previously).

bstrGroupName Optional String

Name of target Workspace.

If omitted, method loads workbook to “My Workspace” of signed-in user.

Note: requires Pro-account, signed-in user must have edit rights in target workspace.

Example

' Creates Dataset from ActiveWorkbook in workspace "Finance Team" (if signed in user has access to it)
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, bstrGroupName:="Finance Team", PublishType:=msoPBIExport
 
' Creates PowerBI Report from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIUpload
 
' Creates Dataset from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIExport

Some Details

(more…)