Monthly Archives: July 2017

#Excel Super Links #108 – shared by David Hager

 

Calculating Travel Distance/Time in Power BI using Bing Map API

http://snip.ly/ir4lk?utm_content=buffer784c2&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer#http://datanrg.blogspot.com/2017/07/calculting-travel-distancetime-in-power.html

Creating custom Map Charts using shapes and VBA

https://exceloffthegrid.com/creating-custom-map-charts-in-excel/

WEBSERVICE and FILTERXML functions explained

http://analystcave.com/excel-webservice-filterxml-functions/

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

https://dhexcel1.wordpress.com/2017/07/20/storing-and-running-m-code-from-excel-worksheet-cells-by-david-hager/

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager

https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/

 

#Excel Super Links #107 – shared by David Hager

 

IP Address Mapping in Power BI

https://blogs.msdn.microsoft.com/samlester/2017/07/23/ipaddressmappinginpowerbi/

Table.TransformColumns – alternative in PowerBI and PowerQuery in Excel

http://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-powerbi-and-powerquery-in-excel/

Painting with Excel

https://www.sumproduct.com/blog/article/painting-with-excel

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

https://dhexcel1.wordpress.com/2017/07/20/storing-and-running-m-code-from-excel-worksheet-cells-by-david-hager/

#Excel Short and Sweet Tip #27 (Use Worksheet UDF to Add Background to #Excel Worksheet) by David Hager (Try it, you’ll like it!)

https://dhexcel1.wordpress.com/2017/07/17/excel-short-and-sweet-tip-27-use-worksheet-udf-to-add-background-to-excel-worksheet-by-david-hager/

 

#Excel Super Links #106 – shared by David Hager

 

Excel SUM Function in Detail

https://www.get-digital-help.com/2017/07/22/excel-sum-function/

Analyze Public Facebook Groups in #PowerBI

https://datachant.com/2017/07/22/analyze-public-facebook-groups-powerbi/

Translate RGB codes to HEX #rrggbb

http://www.vbaexpress.com/kb/getarticle.php?kb_id=840

Excel Custom Number Format Guide

http://www.myonlinetraininghub.com/excel-custom-number-format-guide

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

https://dhexcel1.wordpress.com/2017/07/20/storing-and-running-m-code-from-excel-worksheet-cells-by-david-hager/

 

#Excel Super Links #105 – shared by David Hager

 

Drop Buttons on VBA UserForm Controls

https://peltiertech.com/drop-buttons-vba-userform-controls/

Power Query Unpivot Scenarios

http://www.myonlinetraininghub.com/power-query-unpivot-scenarios

Reduce Development Lag in Power Query

https://www.excelguru.ca/blog/2017/07/20/reduce-development-lag/

Automatic Default Number Formatting in Excel Pivot Tables

https://www.excelcampus.com/pivot-tables/automatic-default-number-formatting/

#Excel Short and Sweet Tip #27 (Use Worksheet UDF to Add Background to #Excel Worksheet) by David Hager

https://dhexcel1.wordpress.com/2017/07/17/excel-short-and-sweet-tip-27-use-worksheet-udf-to-add-background-to-excel-worksheet-by-david-hager/

#Excel Super Links #104 – shared by David Hager

 

Get the File Name of the Current Excel Workbook

https://exceloffthegrid.com/get-the-file-name-of-the-current-workbook/

Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

https://www.youtube.com/watch?v=iWsL5gM962U

Excel Pop Up Selector Tool With Slicer

http://blog.contextures.com/archives/2017/07/20/excel-pop-up-selector-tool/

Use Webservice functions to Automatically Update Excel 2013 Spreadsheets with Online Data

https://blogs.office.com/en-us/2013/03/21/use-webservice-functions-to-automatically-update-excel-2013-spreadsheets-with-online-data/

#Excel Short and Sweet Tip #27 (Use Worksheet UDF to Add Background to #Excel Worksheet) by David Hager

https://dhexcel1.wordpress.com/2017/07/17/excel-short-and-sweet-tip-27-use-worksheet-udf-to-add-background-to-excel-worksheet-by-david-hager/

#Excel Super Links #103 – shared by David Hager

 

DAX KEEPFILTERS Plus Bonus Complaining!

http://tinylizard.com/dax-keepfilters/

When to Use SUM() vs SUMX()

https://exceleratorbi.com.au/use-sum-vs-sumx/

SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel

http://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquery-excel/

Related Distinct Count in DAX

http://www.daxpatterns.com/distinct-count/

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

https://dhexcel1.wordpress.com/2017/07/20/storing-and-running-m-code-from-excel-worksheet-cells-by-david-hager/

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