Cube Formulas · Excel · Power BI

Returning Tables From A Single Excel Cube Formula Connected To Power BI

After my post on tuning Excel cube functions last week there was some (justified) moaning on Twitter about their performance. This got me thinking about other things you could do with cube functions that might improve performance and I had a crazy idea…

Late last year two new DAX functions were introduced which didn’t get much attention: ToCSV and ToJSON. They allow you to evaluate a table expression and return the result as text in either CSV or JSON format, for example from a measure. They are intended to make debugging easier. What I realised is that you can use ToCSV to return a table of data in CSV format in a single Excel cube formula in a cell in your worksheet and then use dynamic arrays to turn that back into a table in Excel.

Using the dataset from last week’s post, with the following tables:

…I created the following measure:

MyTable =
        "Sales Amount", [Sales Amount],
        "Target Amount", [Target Amount]

You can see it return a text value in CSV format from the following Power BI report:

I then created a connection to the published dataset from Excel and using a single formula in cell B3 and two slicers connected to the Product and Continent fields was able to create the following report:

Here’s the formula in B3:

TEXTBEFORE(TEXTAFTER(HeaderRow, "["),"]"),
VSTACK(RemoveSquareBracketsFromHeaders, DataRows),

Note: you also need to click the Wrap Text option on the ribbon for this cell for this to work properly.

There’s a lot going on here but the Excel Let function makes it easy to break the formula up and understand it. Here’s what each step in the formula does:

  • CSV uses the CubeMember and CubeValue functions to get the result of the MyTable measure for the current selection in both slicers.
  • SplitToCells takes the single text value returned by the previous step and uses the TextSplit function to turn it back into an array
  • HeaderRow finds the first row in this array, which contains the column headers using the Take function
  • DataRows finds all other rows, which contain the data in the table using the Drop function
  • RemoveSquareBracketsFromHeaders takes the contents of HeaderRow and uses the TextBefore and TextAfter functions to find the names of each column and measure, which the ToCSV function returns between square brackets (for example it takes ‘Country'[Country] and returns Country, and takes [Sales Amount] and returns Sales Amount)
  • Recombine then takes the new header row with the cleaned names and appends the array from DataRows underneath it, using the VStack function

This formula is a great candidate for use with the Lambda function so it can be easily reused.

There are a number of reasons this technique is interesting, even if it’s slightly hacky. First of all since the table is returned from a DAX expression that you write, and it’s returned by a single query, it could result in faster performance. Second, instead of just one cell returning one table, you can combine it with CubeMember functions to return multiple tables. In this example C3 and G3 contain CubeMember functions that the tables immediately below them refer to:

I haven’t tested this with very large tables. Since there is a limit on the amount of text that a measure can return then there is a limit on the size of the array that can be displayed; I’m also not sure what the performance overhead of the Excel formula to convert the text to the array is.

11 thoughts on “Returning Tables From A Single Excel Cube Formula Connected To Power BI

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      In most cases a PivotTable is fine, but this approach could give you better performance because a) the table is generated with a DAX expression, not MDX and b) you have control over that DAX expression

  1. Hi, interesting article – especially I overlooked the new LET and LAMBDA functions in excel. But to solve the problem of getting a data table in excel from power bi isn’t it better to use a table with a data connection to power bi and query the data using dax?

    What I mean: if you have a pivot table with connection to PBI and you execute a drill down on a measure (which is not in a measure table!) then excel creates a new sheet with a table with a connection to PBI. If you open the query details (via context menu) you will find the MDX query which you can replace with any DAX query.

    Ok…while I am writing this question I see the difference…. When I use this functionality and want to give my customer a possibility to work with slicers to change the result, I need to use VBA… Using your solution I do not have to do that….


    1. Hi Holger,
      you can write a VBA Function to get data from Power BI with no limits.
      The slicer could manipulate the input Dax of this function.

      =getPowerBIData(“ConnectionName”,”Evaluate topn(10,’SalesData’)”)

      This is the VBA:

      Public Function getPowerBIData(ConnName As String, DaxStmnt As String)

      Set c = CreateObject(“ADODB.CONNECTION”): Set r = CreateObject(“ADODB.RECORDSET”)

      c.Open Mid(ThisWorkbook.Connections(ConnName).OLEDBConnection.Connection, 7)
      r.Open DaxStmnt, c

      getPowerBIData = Application.WorksheetFunction.Transpose(r.getrows)

      End Function

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      No, not for a single cell but there’s no reason you can’t return a few slightly smaller tables

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.