Tag Archives: Data Model

The DAX ENDSWITHX Function Equivalent

I remembered the excellent articles written by Rob Collie at powerpivotpro.com on the CONTAINSX function equivalent:

http://www.powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-table-2/

and

http://www.powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/

I decided that it was time to extend this solution to search at the end of a string. In particular, I started to look at my bank statements and I had a need to filter all payments that were a fixed monthly payment. So, Rob’s formula shown below worked great for this:

= SUMX (MList,FIND (MList[MonthlyPay],Payments[Type],, 0 ) )> 0

where MList is a linked list [MonthlyPay] of the search strings those fixed payments, and Payments[Type] is a column in the table for my bank statement. I was able to use this Boolean result to filter my pivot table to afford the desired result.

The payment descriptions are space delimited, and I also wanted to return all searches that look at only the last string in the space delimited string. The following formula accomplishes this.

 = SUMX( MList, FIND( MList[MonthlyPay], MID( [Type], FIND( “~”, IFERROR( SUBSTITUTE( Payments[Type], “”, “~”, LEN(Payments[Type]) -LEN(SUBSTITUTE(Payments[Type],””,””)) ), “~” ), , 1 ), 255 ), , 0 ) ) >0

The internal SUBSTITUTE function creates a string with only the last space in the original string replaced with a “~”, the position which can then be located by the FIND function.

In cases where it may be necessary to search the end of a string that uses another delimiter, it may be desirable to automatically change the delimiter in this formula. By using a one cell linked table, the value in Delimiter[delim] can be changed in the table and then updated in the DataModel. The result is shown in the formula below.

= SUMX( MList, FIND( MList[MonthlyPay], MID( [Type], FIND( “~”, IFERROR( SUBSTITUTE( Payments[Type], VALUES(Delimiter[delim]), “~”, LEN(Payments[Type]) -LEN( SUBSTITUTE( Payments[Type], VALUES(Delimiter[delim]), “” ) ) ), “~” ), , 1 ), 255 ), , 0 ) ) >0

Although I cannot claim to know every DAX formula ever made, I am fairly certain that this represents the 1st example of parameterizing a text character in a DAX formula.

BTW, I apologize for the brevity of this article and the lack of any pictures showing visually what I am describing.

It is possible that I drifted off of the subject of an actual ENDSWITHX function equivalent, so to correct that, I offer the following formula.

= SUMX( MList, FIND( MList[MonthlyPay], RIGHT([Type],MAX(MList[LenVal])), , 0 ) ) >0

where a column in the MList table [LenVal] uses the formula  =LEN([MonthlyPay]) to calculate the number of text characters for each search string. The maximum value is used with the RIGHT to return a searchable string from the end of the [TYPE] string. If the [type] string has is smaller  than the max len value of the search string, it returns the shorter string without producing an error.

HTH!

Advertisements

Excel 2013: Power Query OmniQueries. Dynamic And Refreshable Connections To The Data Model

Inspired by Chris Webb’s recent post on loading Power Query (PQ) M code from text files

http://cwebbbi.wordpress.com/2014/02/04/loading-power-query-m-code-from-text-files/

and by Dick Moffat’s articles on refreshable Data Model connections in Excel 2013

http://dmoffat.wordpress.com/2013/08/14/building-a-flexible-and-efficient-client-side-powerpivot-solution-in-excel-2013-2/

and

http://dmoffat.wordpress.com/2013/08/21/how-you-can-change-to-another-connection-type-in-powerpivot/

I have come up with a method to make PQ queries to the Excel 2013 Data Model dynamic and refreshable.

[BTW, in order to better understand this post, you might want to read the referenced articles at the links shown above before proceeding, because I sense that this article is not my best literary work]

Chris discovered that by using the M code Expression.Evaluate function, a PQ script stored in a text file can be read by PQ and applied as a query. I realized that if I could create a way for the text filename string to be picked from a list in Excel and read into PQ, I would have a dynamic and refreshable way to feed any PQ query into a single connection as per Dick’s example with native Excel queries.

To my knowledge, the only information that can be read from an Excel workbook by PQ resides in a table, so I made a single cell table (Table3) as shown below.

The PQ query that uses the text filename string is shown below.

PQ_Refresh1

Let

// Get filename from an Excel single cell table

QSource = Excel.CurrentWorkbook(){[Name=”Table3″]}[Content],

QText = QSource{0}[DynCell],

//Load M code from text file

Source = Text.FromBinary(File.Contents(QText)),

//Evaluate the code from the file as an M expression

EvaluatedExpression = Expression.Evaluate(Source, #shared)

in

EvaluatedExpression

By applying data validation to the single cell in Table3, the value in that cell can be changed based on the selection of a filename from the validation list (shown in column T in the Figure). In this example, cell P4 is the cell where the data (list) validation is applied. The goal is to trigger a calculate event when the value in that cell is changed, which will run the following procedure.

Private Sub Worksheet_Calculate()

If ActiveCell = Range(“p4″) Then

For Each conn In ThisWorkbook.Connections

conn.Refresh

Next

End If

End Sub

Unfortunately, changing a value in a cell based on a data validation list does not trigger a calculation of the worksheet. However, there is a kludgy trick that can be used to cause a calculation to occur. By placing the following formula in a cell (in this case F1, as shown in Fig. 1), the event procedure will run when the validation list is used in P4.

=COUNTA(P1:P5)

So, either of the two scripts stored in text files are run by selecting the filename string in P4 which ends up in  QText in the main query. Essentially the same PQ script used in Chris’s post was used in this example (Table4, in Figure) for DynamicQuery1.txt. For the query DynamicQuery2.txt, I changed script slightly to:

let

Source = Excel.CurrentWorkbook(){[Name=”Table4”]}[Content],

GroupedRows = Table.Group(Source, {}, {{“Average of Sales”, each List.Average([Sales]), type number}}

in

GroupedRows

The process works equally well whether the query is loaded on a worksheet or directly into the Data Model. Since this refresh scenario is in a workbook designed to have only one connection, a generic macro is used in the refresh process.  In more complex workbooks, the VBA procedure can obviously be crafted to selectively refresh only the “OmniQueries” present in the workbook.

Finally, although I have not tested this, it appears likely that this same methodology can be used with SQL statements in PQ in the manner described in this Microsoft article:

http://office.microsoft.com/en-us/excel-help/import-data-from-database-using-native-database-query-HA104123758.aspx

The SQL.Database M function shown in the figure at this link uses a SQL query string, which can likely be called by the Expression.Evaluate function using the same process described in this post.

Let me know if you find this technique useful, and thanks again to Chris and Dick for paving the road.