Excel Super Links 1-150

This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Excel Super Links 1-150

 

 

Adding Multiple DAX Measures to Non-PowerPivot Versions of #Excel using an User-Defined VBA Function

 

 

In this article,

http://dailydoseofexcel.com/archives/2017/07/10/look-ma-no-powerpivot/

Jeff Weir pointed to a video made by Mike Girvin about adding measures to non-PowerPivot versions of Excel (link below)

https://www.youtube.com/watch?v=FVVK-8QZC1M&t=422s

Mike demonstrated how measures can be added to a data model in these “disabled” version through pivot table options. Please view this video to see how Mike did it.

The link to the working file for this video will be referred to in this article (Thanks, Mike!).

https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1269Finished.xlsx

You can download this file and reproduce the technique presented here.

Although it is not well-known, Microsoft started at Excel version 2016 (Office 365) marketing versions that do not have PowerPivot capability. For details on this, see:

https://blogs.office.com/en-us/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

So, this article is dedicated to those who purchased non-PowerPivot versions of Excel 2016, although the technique presented here will work on any version of Excel 2013 or greater.

Jeff Weir mentioned in his article that since some Excel 2016 versions did not have the full-blown PowerPivot capability, and that VBA could be used to build a user interface to the data model. Well, I have not created a UI, but I have made a way to add multiple measures to the data model using an user-defined function. The code for the VBA function is shown below. To use this, add astandard module in the VBE and then save the workbook as .xlsm. Then, copy/paste the code into the module.

 

Function AddMeasure(TableName As String, MeasureName As Range)

Application.Volatile False

With ActiveWorkbook.Model

For Each mCell In MeasureName

mFormat = mCell.Offset(0, 2).Value

.ModelMeasures.Add mCell.Value, .ModelTables(TableName), mCell.Offset(0, 1).Value, _

Switch(mFormat = “Boolean”, .ModelFormatBoolean, mFormat = “Currency”, .ModelFormatCurrency, _

mFormat = “Date”, .ModelFormatDate, mFormat = “DecimalNumber”, .ModelFormatDecimalNumber, _

mFormat = “General”, .ModelFormatGeneral, mFormat = “PercentageNumber”, .ModelFormatPercentageNumber, _

mFormat = “ScientificNumber”, .ModelFormatScientificNumber, mFormat = “WholeNumber”, .ModelFormatWholeNumber), _

mCell.Value

Next

End With

AddMeasure = “DONE”

End Function

Then, place the following information in the range D10:F14.

 

NetRevenue	SUMX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	DecimalNumber
MaxRevenue	MAXX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	PercentageNumber
MinRevenue	MINX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	Currency
AverageRevenue	AVERAGEX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	General
CountOfRevenue	COUNTAX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2))	General

 

To run this as a worksheet formula, type this formula in any cell.

=AddMeasure(“fTransactions”,D10:D14)

This will add the 5 measures to the data model, as shown in the Pivot Table Fields list.

After the 5 measures are added to the pivot table, the resulting pivot table will look like this.

Of course, the DAX formulas to be added have to return valid results, or the procedure will fail.

This powerful technique is yet another reason why users should not completely abandon Excel for Power BI desktop, as discussed in this article at powerpivotpro.com

https://powerpivotpro.com/2017/09/excel-is-still-the-best-tool-for-teaching-dax/

And, this technique does not HAVE to be run from a UDF, but I am still amazed that it can. I am sure that you will find this very useful.

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

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.

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.

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.

 

Generating Random Initials with DAX in Power BI by David Hager

After publishing a short article about generating random initials in Excel

https://dhexcel1.wordpress.com/2017/05/18/excel-short-and-sweet-tip-14-generating-random-initials-by-david-hager/

I wanted to see if the same formula worked in Power BI. When I entered the aforementioned formula into a calculated column in Power BI desktop, I got an error message. After some research, I discovered that was no CHAR function in the DAX function reference. Then, I remembered that Chris Webb showed examples of the new UNICHAR function at the following link.

https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-data-visualisation/

And, Chris discovered that the UNICHAR function was completely undocumented and was probably introduced in the April 2017 update. So, I changed my random initials formula using UNICHAR instead of CHAR, and it worked.

=UNICHAR(INT(RAND()*26)+65)&UNICHAR(INT(RAND()*26)+65)&UNICHAR(INT(RAND()*26)+65)

Now, you can use this in your Power BI projects.

It is still a mystery how DAX could be around for 8 years and not have the CHAR function (or its equivalent).

 

Measuring Distance in Power BI Desktop

Please contact me with any Excel Project work you may have. Written 10/28/2020

Email address: dhExcel1@gmail.com

Among the many new BI features introduced with the recent release of the free version of the Microsoft Power BI desktop is the “new” trigonometric DAX functions such as SIN and COS. Of course, these functions have been around forever in Excel, but for some reason were not included in the first two major releases of Power Pivot. So, now they are available to the DAX language (and also in Excel 2016, if you have a spare computer handy to use in the preview version), and as such can be used to calculate straight-line distances between two points on the Earth as defined by their latitude and longitude coordinates. Also, in the formula, the constant PI()/180 is required a number of times, so the implementation of the new DAX variable comes in very handy here. In Power BI, if you add a new measure named PI_D180 as equal to PI()/180 (0.0174532925199433), you can use that measure in your DAX formula for distance, as shown below.

Distance = ACOS(SIN(Distance[Latitude_1]*[PI_DIV180])*SIN(Distance[Latitude_2]*[PI_DIV180])+COS(Distance[Latitude_1]*[PI_DIV180])*COS(Distance[Latitude_2]*[PI_DIV180])*COS((Distance[Longitude_2]*[PI_DIV180])-(Distance[Longitude_1]*[PI_DIV180])))*3959

Hope that you find this useful!