PowerPivotPro

PowerPivotPro is Coming to Boston

May 15 - 17, 2018

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MAY 15 - 16

Foundations: Power Pivot & Power BI

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!

Overview:

  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!

Overview:

  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.

MAY 17

Level Up Series: Power Query for Excel & Power BI

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!

Overview:

  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
Boston Public Training Classes - PowerPivotPro
PowerPivotPro Logo

Function 2 Banner

My name is Mathew Herring Jack, and I am a member of Traditional Excel Users Anonymous.

As a Traditional Excel User, I could not stop copying and pasting downloaded reports. Even when my manager diagnosed me with inaccurate month-end reporting, I could not stop using external workbook links. I admitted my reckless use of nested IF statements was hurting others, yet I knew I would do it again. I still don’t know why I needed to type FALSE at the end of my VLOOKUP’s – it makes no sense.

Image result for fight club anonymous

Yet, here I am today, using Excel as much as almost anyone on the planet, now many years SUMPRODUCT free. I almost never use the CTRL key anymore. In fact, I use the mouse a LOT in Excel now. A Traditional Excel User could never admit such a thing.

How is it possible to still be here today saying that I am an Excel expert and yet I don’t even use the formula bar anymore!

I found a way out. By following the 28-step program as described in the Big Book of Traditional Excel Users Anonymous and living in the disciplines of DAX and M every day, I can remain abstinent from Traditional Excel and the allure of absolute referencing.

I now know how to live without Traditional Excel. The compulsion to show off even my array formulas has been removed. My progressive illness has been arrested. My disease has been put into remission. I now dedicate my life to traveling the country helping others recover from the affliction… See you in Boston!

And Back To Your Regular Scheduled “Programming”

Today we’re revisiting my gateway drug, and we’re going to use the Power BI web connector to import every Excel Function to go alongside the DAX reference we built in part 1 of The Great Function Project.

If you missed part 1, go back and follow the steps. It should take all of about 5 minutes to get caught up. Part 2 should only take 5-10 minutes on top of that.

The general idea is that we’re together co-developing a Power BI reference that contains information on every DAX, Excel and M function available. We’ll analyze the similarities and differences between the three languages and will leave you with a useful resource to help you and your organization transition from Traditional Excel to Modern Excel.

If you did follow along with Part 1, re-open your workbook and insert coin to continue.

Web Scraping? Sounds Painful

Not everyone wants their online content harvested, but if you want to enrich your data model, there is a vast data source that you might be ignoring – the web. Need performance reporting adjusted from the population? Can do. Want to quantify the impact of a Patriots win on your Sunday sales? There’s a source for that.

Need every Excel function available? Check this link out. 

Excel functions

 

Scroll down the page and look at all 471 of those Excel functions! Your scrolling finger is getting sore? OK then, I have a better idea. Let’s use this page as a data source and save it in Power BI for later.

Go to Get Data –> Web

DAX Function Reference

Paste this url into the next screen and hit OK.

https://support.office.com/en-us/article/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

From web

Power BI looked at that webpage and what it found were two important elements – Document and Table 0. Table 0 looks to have all the good information in it, so check the box next to it and hit ‘Edit.’

Table 0

Commence Right Clicking

Rename your new Query to Excel and let’s do a little data cleanup… The goal here is to have three columns – Function Name, Function Group and Function Description AND to have Function Name is a similar enough format to the DAX Query that later on we’ll be able to link them together by a relationship.

.little data cleanup

1) Right click on Function Name column and select ‘Replace Values.’ Enter “ function” in as the Value To Find – note the space preceding the word ‘function.’ Hit OK.

replace values

2) Right-click on the ‘Type and description’ column and select Split Column –> By Delimiter. Select ‘Colon’ as the delimiter and check the ‘Left-most delimiter’ button. Hit OK.

split column

Looking better, but happiness has not been achieved. More right-clicking is necessary. looking better

 

 

3) Right-click column ‘Type and description.1’ and select Rename Columns. Rename to ‘Function Group.’ Right-click column ‘Type and description.2’ and select Rename Columns. Rename to ‘Excel Function Description.’

4) Right-click the ‘Excel Function Description’ column and select Transform –> Trim. This will get rid of spaces at the beginning or end of the column.

Excel – The Gateway Drug

I was a Finance guy. You wanted a business model? I could do that. Need a merger model to bolt onto that? I could do that too. Need a GAAP-compliant quarterly financial statement consolidations package that integrated with Word? Give me a few days. Traditional Excel put beer in my fridge for 15 years.

I’m not someone who’ll necessarily step outside my comfort zone. If I’m pushed outside of it, then I’ll run with it and thank you later. But in 2012, nobody was going to push me out of my Excel comfort zone. DAX was around, but let’s just say that its existence wasn’t well known, let alone how to find it.

So lucky for me, there was a lot of overlap between DAX and Excel – I didn’t need to step out of my comfort zone.

How much overlap EXACTLY though? Well, let’s find out… we’ll need to create a lookup table between our DAX and Excel tables.

Right-click on the DAX query from Part 1 and select ’Reference.’ Then rename that new query to ‘Function Lookup Table.’

reference

Before we do the next step, go back and make sure that you have a column named ‘Function Name’ in both your DAX Query and your Excel Query. As a reminder, Power Query is case sensitive, so it needs to be exactly the same in both. My queries looked like this:

DAX Query

DAX Query

Excel Query

Excel Query

Now left click on Append Queries in the top right. Then select Excel from the drop-down menu Table to Append. If you get a message about Privacy settings just check ‘Ignore’ and move on.

Click here

You should now have a table that looks something like this:

looks like this

This is more information than we need for the basic lookup table we are aiming for here.

Right Click on the Function Name column and select ‘Remove Other Columns,’ ’then right click again and select Remove Duplicates and now you have the most vanilla lookup table possible.

function name

Hit ‘Close and Apply’ and Power BI will likely create the relationships for you, but if not just replicate this structure, creating the relationship using the Function Name columns..

function lookup table

Finally, time to write some DAX.

# of DAX Functions =
COUNTROWS ( ‘DAX’ )

# of Excel Functions =
COUNTROWS ( Excel )

# of Functions in Both DAX & Excel =
CALCULATE (
    COUNTROWS ( ‘Function Lookup Table’ ),
    FILTER (
‘Function Lookup Table’,
[# of Excel Functions] = 1
&& [# of DAX Functions] = 1
    )
)

% of DAX already in Excel =
DIVIDE ( [# of Functions in Both DAX & Excel], [# of DAX Functions])

 

And there we have our answer, 48.09% of the DAX language was already present in Excel.

Now, this doesn’t tell the whole story at all, but regardless of the numbers, getting started with DAX is within the comfort zone of anyone that’s written a SUM in Excel and lived to tell the tale.

Excel isn’t going anywhere as the world’s most used BI tool – but in the years to come in might just be used a little less…. traditionally.

As for Power BI, in my opinion, this extensibility is what drives it from nowhere to being the leader in BI in the cloud  with a $500 million annualized billings run rate for its 2018 fiscal year. Achieving these milestones is a lot easier when 1 billion people already know 48.09% of your new product’s language.

Next up in the series is M…. I’ll be posting that on Tuesday 17th April. The lack of intellisense in M is was the primary reason I started the Great Function Project, so it’s kind of the Grand Finale.

Did you find this article easier to understand than the average “tech” article?

We like to think that is no accident.  We’re different.  First of a new breed – the kind who can speak tech, biz, and human all at the same time.

Want this kind of readily-absorbable, human-oriented Power BI instruction for your team? Hire us for a private training at your facility, OR attend one of our public workshops!

  Subscribe to PowerPivotPro!
X

Subscribe

Mat Herring

Mat is a US CPA and Australian Chartered Accountant with 15 years’ experience across global financial services giants and bootstrapping startups alike. During that period, the Microsoft Power Suite became the secret weapon that allowed Mat to transition from competent finance manager to cross-functional CFO. A real life example of the career empowerment that can take place when a bean counter gets hands on with the analytics. 

This Post Has 19 Comments

  1. You should make a short YouTube video of this for people who don’t follow this blog religiously – episode #1 is EXACTLY what I’ve been wanting for quite some time, because it’s easy to search but also I can add my own notes or code snippets in for better explanations.

  2. Mat,
    My original shows errors in the Query. It gives this error: DataSource.Error: AnalysisServices: A connection cannot be made. Ensure that the server is running.
    Details:
    DataSourceKind=AnalysisServices
    DataSourcePath=localhost:50428;27907f4a-e313-4d3e-8c7e-d4396dae33aa

    No matter how I try to refresh the connection it refuses to work and doesn’t allow me to work on the Second Part of the project. The error occurs in the first part of the source which is = Table.SelectRows(Source, each ([ORIGIN] 1))
    Here is my query:
    let
    Source = AnalysisServices.Database(“LOCALHOST:50428”, “27907f4a-e313-4d3e-8c7e-d4396dae33aa”, [Query=”select [FUNCTION_NAME], [DESCRIPTION], [ORIGIN], [LIBRARY_NAME], [INTERFACE_NAME] from $SYSTEM.MDSCHEMA_FUNCTIONS”]),
    #”Filtered Rows” = Table.SelectRows(Source, each ([ORIGIN] 1)),
    #”Renamed Columns” = Table.RenameColumns(#”Filtered Rows”,{{“FUNCTION_NAME”, “Function name”}, {“INTERFACE_NAME”, “Group”}})
    in
    #”Renamed Columns”

    1. Kristi: Re-run DAX Studio and update the query with the revised local host and Catalog_Name fields. Worked for me.

      1. Ham,
        I have tried rerunning it several times. My query just refuses to revive. Also, if Dax Studio had solved the issue, would that step have to be repeated every time?

        1. Yes, you will get a new host each time and if you crack open DAX Studio again and retrace your steps, you should be able to revive the connection as Ham says.

          I would suggest disabling the refresh within Power Query Editor, as Matt says the data isn’t going to change too often. It was a late addition/edit, but I added this step to the end of Part 1.

          As a last resort, running through the whole of Part 1 again, does not take a long time.

      2. Thank you! I was getting the same error, this worked for me. Hopefully we don’t have to do that every time, but admittedly the data behind this isn’t going to change all that often, not the worst thing ever if we do.

        1. This happens every time for me. My worksheet is on OneDrive so it can be shared in the group – maybe there’s a new local host ID each time?

  3. hola Mat! genial mucho mas facil que la metadata jejeje probare cargar el lenguaje M desde la web. gracias excelente articulo, bellisimo.
    gracias por este aporte. saludos 🙂

  4. I finally managed to get the connections refreshed but now, my numbers and percentages are different than yours. I show 262 dax functions, 471 excel functions, 252 in both Dax and Excel for a percentage of 96.18. I have removed duplicates.

    1. Interesting, even removing duplicates did not fix this. I had to trim and clean and remove duplicates one more time to get the correct numbers. I did trim and clean on both original tables.Seems buggy for some reason. Perhaps I am due an update.

      1. Hi Kristi
        Sorry for the late response. If you’re showing 252 (2x 126) then I would have to assume that you are showing duplicates in the Function Name column.

        1. Hi Matt,
          I did remove the duplicates. I also transformed for case but it still populated duplicates. I had to transform for case, trim, and clean and then remove duplicates again. I followed your steps exactly but the write up didn’t list the steps of trim and clean so I had not completed them until my results were different.
          I was due an update but I don’t see how that made the difference.

  5. Lovely post! However it is just a pitty that Power Query do not alow to scrap data from password protected websites, where there are, most of the times, alot of data gathered and needed to the business. 🙂

  6. Thank you for undertaking this project. I am up to date, so far so good. The problem it addresses are problems I face everyday as I face everyday as a traditional Excel user my self. Is it your plan to load the syntax of the function(s) as well as the Name and Definition?

    1. This is certainly possible, but no my plan was really to just to engage with our readers and build something useful together, to do it quickly and to demystify a few “scary” techniques related to Power BI like DAX Studio, Web Scraping, ETL, manufacturing lookup tables and of course the DAX &M Languages themselves.

      Then my hope was that some of our more resourceful readers might take the project beyond where I had, and once the series is complete, that some of our readers might post share their creativity.

Leave a Comment or Question