Tag Archives: Power Query

Run Your Power Query M Code Procedures in #Excel Worksheet Cells by David Hager

 

The ability to reuse Power Query M procedures has been for the most part reserved for those capable of purchasing the full-blown Power BI package. Wouldn’t it be great for anyone owning Excel to benefit from a way to store and run M procedures? Well, I believe that you have come to the right place.

The inspiration behind this technique came from Chris Webb’s article for running M code from text files.

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

In the comments section of that article, there was a discussion of the portability of the text files to other potential users. I then made the following comment: “You could obviously store the entire M code in worksheet cells, if you had to.” Well, nothing was done with this idea, and I had forgotten about it until now. I decided to use a named cell to hold the M code, as described in this article.

https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

Chris helped me to work through a few issues I had in making this. Thanks for your help, Chris!

Here is the M code that utilizes the M procedure stored in a named range Excel cell (MCode).

let

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

ChangeDataTypes = Table.TransformColumnTypes(Source,

{“Column1”, Text.Type}),

GetMCode = ChangeDataTypes{0}[Column1],

EvaluatedExpression = Expression.Evaluate(GetMCode, #shared)

In

EvaluatedExpression

I decided to use Matt Allington’s calendar table M code for testing (great M code, Matt!)

https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

When I copied the code from the article, I found that there was no way to paste the entire code into 1 cell, mainly because multiple lines of text are viewed by Excel as one line per cell. So, I ended up with 25 lines of code in cells A1:A25. Thankfully, the TEXTJOIN function provides a way to assemble those lines of code so that they are readable by Power Query. The formula =TEXTJOIN(” “,,A1:A25) returns a single string with spaces between the lines of code, which appears to be necessary for the code to run correctly from a single cell. In this case, the named range cell is called MCode (cell C1). So, the main code pulls in the single cell table and transforms it into text. That M code text was run using Expression.Evaluate to return the Power Query query calendar table. The named cell MCode can either contain the TEXTJOIN formula associated with the lines of code desired or you can Copy, Paste Special Values to make a string that can be stored. In the example file, cell C4 contains the code string for the main M procedure, which you can copy/paste into the Power Query Advanced Editor to run. The stored M procedures (in this case only one – in cell C5) can be either placed in the MCode cell or a formula can be used to return the desired procedure.

I have removed the calendar table query result, but you can reproduce it if you like. I do not plan to make a storehouse of M procedures from this (at least, not one I am willing to share😊), so feel free to use this technique as you desire. IMHO, the ability to potentially store and use 1000’s of M procedures in a portable way is exciting.

The example file can be downloaded here.

MCode_RunFromNamedCell

Advertisements

Storing and Running Power Query M Code from #Excel Worksheet Cells by David Hager

 

The ability to reuse Power Query M procedures has been for the most part reserved for those capable of purchasing the full-blown Power BI package. Wouldn’t it be great for anyone owning Excel to benefit from a way to store and run M procedures? Well, I believe that you have come to the right place.

The inspiration behind this technique came from Chris Webb’s article for running M code from text files.

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

In the comments section of that article, there was a discussion of the portability of the text files to other potential users. I then made the following comment: “You could obviously store the entire M code in worksheet cells, if you had to.” Well, nothing was done with this idea, and I had forgotten about it until now. I decided to use a named cell to hold the M code, as described in this article.

https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

Chris helped me to work through a few issues I had in making this. Thanks for your help, Chris!

Here is the M code that utilizes the M procedure stored in a named range Excel cell (MCode).

let

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

ChangeDataTypes = Table.TransformColumnTypes(Source,

{“Column1”, Text.Type}),

GetMCode = ChangeDataTypes{0}[Column1],

EvaluatedExpression = Expression.Evaluate(GetMCode, #shared)

In

EvaluatedExpression

I decided to use Matt Allington’s calendar table M code for testing (great M code, Matt!)

https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

When I copied the code from the article, I found that there was no way to paste the entire code into 1 cell, mainly because multiple lines of text are viewed by Excel as one line per cell. So, I ended up with 25 lines of code in cells A1:A25. Thankfully, the TEXTJOIN function provides a way to assemble those lines of code so that they are readable by Power Query. The formula =TEXTJOIN(” “,,A1:A25) returns a single string with spaces between the lines of code, which appears to be necessary for the code to run correctly from a single cell. In this case, the named range cell is called MCode (cell C1). So, the main code pulls in the single cell table and transforms it into text. That M code text was run using Expression.Evaluate to return the Power Query query calendar table. The named cell MCode can either contain the TEXTJOIN formula associated with the lines of code desired or you can Copy, Paste Special Values to make a string that can be stored. In the example file, cell C4 contains the code string for the main M procedure, which you can copy/paste into the Power Query Advanced Editor to run. The stored M procedures (in this case only one – in cell C5) can be either placed in the MCode cell or a formula can be used to return the desired procedure.

I have removed the calendar table query result, but you can reproduce it if you like. I do not plan to make a storehouse of M procedures from this (at least, not one I am willing to share😊), so feel free to use this technique as you desire. IMHO, the ability to potentially store and use 1000’s of M procedures in a portable way is exciting.

The example file can be downloaded here.

MCode_RunFromNamedCell

Excel: Using Power Query to Return All Words From a List of Letters Including Wildcards by David Hager

I happened to run across a web site that returns all of the words by using a set number of letters.

http://wordfinder.yourdictionary.com/unscramble/

All credit for the working of the query demonstrated here goes to the aforementioned website.

So, I copied the URL of the query and found a way to use it in Power Query. I needed a way to add the string to the query, so I created an Excel table (named Letters) where the string would originate from.

Then, I was able to create M code that used the concatenated query and returned the output to the worksheet.

See:

let

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

QText = QSource{0}[#”What Letters Do You Have?”],

Webstring = “http://wordfinder.yourdictionary.com/unscramble/”&QText,

Source = Web.Page(Web.Contents(Webstring)),

#”Removed Bottom Rows” = Table.RemoveLastN(Source,1),

#”Expanded Data” = Table.ExpandTableColumn(#”Removed Bottom Rows”, “Data”, {“Word”, “Scrabble® Points”}, {“Word”, “Scrabble® Points”}),

#”Removed Columns” = Table.RemoveColumns(#”Expanded Data”,{“Caption”, “Source”, “ClassName”, “Id”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns”,{{“Word”, type text}, {“Scrabble® Points”, Int64.Type}})

in

#”Changed Type”

Here is an example of what the worksheet looks like after running it.

pq_scrabble1

I was having trouble refreshing the query, so I asked Excel MVP and Power Query guru Ken Puls http://www.excelguru.ca/ for some help. This is the event procedure he came up with to refresh the query from the ListObjects(“Letters”) table.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, ListObjects(“Letters”).DataBodyRange) Is Nothing Then

ListObjects(“IsWord”).QueryTable.Refresh BackgroundQuery:=False

End If

End Sub

Shortly after making this Power Query Scrabble Words app, I discovered a website where an Excel-based Scrabble game was available.

http://www.dustinormond.com/blog/vba-scrabble/

I started playing this game and connected the letters from that game to the Scrabble Words app with external links. In order to control the process, I used a formula to concatenate the linked letters (in I2:Q2) to make the string needed for the query.

=IF(I3=””,I2,I3)&IF(J3=””,J2,J3)&IF(K3=””,K2,K3)&IF(L3=””,L2,L3)&IF(M3=””,M2,M3)&IF(N3=””,N2,N3)&IF(O3=””,O2,O3)&IF(P3=””,P2,P3)&IF(Q3=””,Q2,Q3)

In the model I am sharing with you, there are no external links for obvious reasons. But, if there were blank tiles, then I could replace the blank with a letter (i.e. – L2 is blank and the letter in L3 would take its place. The only drawback is that the formula in G2 has to be recalculated for the Power Query query to refresh. This can be done by clicking in G2 and hitting Enter.

But, as I was starting work on this article, I went back to the source web site and discovered something I had overlooked. Wildcards (2 of them) can be used in the query! On that site, they use question marks as wildcard characters, but the query I made will only work with an underscore. So, in the following figure, I demonstrate how this is done.

pq_scrabble2

Whether you use this for playing (cheating at) Scrabble or just to use the generated words in some other way, I think that you will find this technique to be very useful.

Unfortunately, I do not have permission from the website for file download, but I still hope that you find this information useful.

 

Dynamic Write-Back to Sync PowerPivot Tables with Date Tables created by Power Query

The following technique is a modification to Chris Webb’s excellent Power Query article located at: http://cwebbbi.wordpress.com/2013/11/19/generating-a-date-dimension-table-in-power-query/

I hope that I am not stepping on something that Chris has already done but not published. Anyway, in order to understand what I am presenting here, you will have to read Chris’s article and download his workbook to follow this.

The M script in the workbook uses one cell input tables to store the beginning and end dates to size the date table as desired. It would be nice to size the date table based on the date range of a fact table in the PowerPivot Data Model. This would be a table that is refreshed periodically and the date range of the fact table would change as a result. Instead of manually updating the one cell input tables after visually determining the minimum and maximum values of the column in the fact table containing the dates, the following technique could be used.

The first step is to create two measures to calculate the min and max values:

MinDateValue:=MIN(Fact[DateKey])

MaxDateValue:=MAX(Fact[DateKey])

Then, if you are not a MDX expert like Chris (and I am definitely not one), create a PowerPivot pivot table and add the two measures to the pivot table. Next, convert the pivot table to cube formulas by highlighting it and then select from the menu Pivottable Tools, Analyze, OLAP Tools, Convert To Formulas. If the upper left cell of your pivot table was cell A1, you will see the following CUBE formulas in A1 and A2 respectively:

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[MinDateValue]”)

=CUBEVALUE(“ThisWorkbookDataModel”,A$1)

The next step is to create a cube formula that is independent of cell references, so by replacing A$1 in the 2nd formula with the first formula, we obtain:

=CUBEVALUE(“ThisWorkbookDataModel”,

CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[MinDateValue]”)

)

This formula will return the smallest date in the fact table. Repeating the steps for the max value gives this formula.

=CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[MaxDateValue]”))

Now, by copying these formulas into the one cell input tables (in the Date Parameters worksheet in Chris’s workbook), a dynamic updating effect for the Data Model is created. When the fact table is updated through PowerPivot with new rows and the Power Query M script is run, the generated data table date range will sync with the date range of the fact table. Once again, thanks to Chris for leading the way in showing the possibilities of Power Query.

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.