Power BI

  • Power Query and Excel files–XLSX vs XLS formats and datatypes

    image

    This is a topic that has been previously covered here by my good friend Ken Puls, but it was just recently that I found this response from Ehren about how Power Query interprets xls files differently to xlsx files and it is certainly worth a blog post to cover this in more detail.

    I highly encourage you to read this post if you use Power Query inside Excel or Power BI against Excel files either in *.xls or *.xlsx

  • Power BI Custom Connector Languages

    image

    Power BI Custom Connectors are something amazing and while they are still in a public preview phase, there are many things that are specific to custom connectors that go beyond what we used to know about M code.

    This post tries to cover how you can make your custom connector have a UI based on the language of your Power BI Desktop installation using the Extension.LoadString function.

    You can follow along by getting my Power BI REST API Custom Connector from here.

  • Guide and Resources for Web Data Extraction with Power BI & Power Query

    With the recent release of the Power Query Extensibility Model or Power BI Custom Connectors, I’ve been thinking about posting an article about the status of Web Scraping with Power BI / Power Query, its limitations and how Custom Connectors are here to provide the functionality that was restricted or limited to us within Power BI / Power Query.

    The goal of this blog post is to serve you as a guide on what you can accomplish with the native functionality of the ‘Web Data’ connector inside Power Query and when you should consider creating a Custom Connector.

  • The Ultimate Calendar Table Creator for Power BI

    Power Query and Power Pivot are AMAZING tools, but if you want to create a Calendar Table with either one of them, you’ll either have to learn how to code in M or DAX, or copy/paste a code that you probably found on the web like this one.

    The problem with that is that every time that you need to create a Calendar Table you need to go through that whole process, and going through either M or DAX code could be intimidating to even a seasoned player. There’s simply no easy or user-friendly interface or portability, but you STILL need a calendar table if you’re trying to use Power BI’s Time Intelligence functions.

    What if there was a single button that could create that Calendar Table for you?

    SNAGHTML456e89

    Imagine a button that sits right in the ‘Get Data’ window and once you click it, it’ll ask your for a few parameters like start date of your calendar table and, once you hit OK, you’ll get EXACTLY the calendar table that you were looking for?

    Well, this is now possible! and it’s all thanks to the Power BI Custom Connectors.

  • Connecting to APIs with Power BI (Power Query)–Part 1 of 2

    image

    Why is it so complex to connect to an API with Power BI or Power Query? The OAuth explained

    It’s easier to explain using analogies, so let’s go with that!

    Analogy: Imagine that you have a gym subscription and this gym also has a VIP lounge, but to get to the lounge you need to head to the front-desk to get authorization for a limited time.

    Using MailChimp as an example:

    • The gym is actually MailChimp – they put all the facilities for you
    • The lounge section is their API and its where you can “access” your data
    • The front-desk authorization is what we know as a Authorization Token, which grants us the chance to “be” in the API

    I’m overly simplifying things here, but the main idea is that the “vip lounge” is the place that the “gym” gives you to do what they give you access to do. They (the gym) could give you access to read, write, delete or modify “data”.

  • Advanced Power Query and other questions from ‘M is for Data Monkey’ readers

    image

    The #1 Power Query book in sales and ratings. THANK YOU everyone Sonrisa

    A little over a year ago, Ken Puls and I published a book called ‘M is for Data Monkey’ which came out from a cycle of iterations over our Live Online Workshop. The book, in essence, is a summarized version of our Workshop with some Key concepts and examples that we used back then in our Power Query Training Workshop. Of course, Power Query has changed and our online workshop has changed as well. We keep our workshop fresh and up to date with the latest and the greatest, but the book is still relevant and will keep being relevant for many years to come even if some of the new UI or feature changes are not reflected in the book.

    It’s crazy how well our book as been received across the globe with over 4.5 stars average on most of the reviews that we receive. Here’s a few of the reviews that some of the readers have left on the Amazon US site:

    image

    image

    image

  • New Functionality in Power BI Desktop–January 2017

    image

    Almost every month the Power BI team delivers a new version of the Power BI Desktop with some new connector, new ways to visualize data and some other ways to work even easier with your data model or your ‘Get Data’ experience.

    The one that I loved this time and that might not be completely visible for most people is the new extract values from a nested list. The whole reason why this might not be visible to most users is that you almost never see a List when working with just the UI of Power Query. So, how do we even get to use this new feature?

    Let’s try with a simple example:

    image

    A table with stats on some marathon races with fields like, Marathon ID, the Name of the participant and the place or rank that the participant got on each marathon

    We would like to work our way from the table on the left to the one on the right.

  • New eBook about Power Query: Secure your FREE copy now!

     

    Data Wizard cover

    YES! – We’ve been working hard on this one and it’ll be a completely free eBook. If you want to get your copy, be sure to sign up to our newsletter here and you’ll be one of the firsts to receive a free copy of this ebook.

  • New Power BI Desktop (fka Power BI Designer)

    image

    (A re-engineered tool with a new Visualizations engine)

    Power BI Desktop is the tool that replaces the Power BI Designer –  in short, it’s the new and optimized version of the Power BI Designer that comes with new features, new integrations and even a completely new take when it comes to the Microsoft policies as they are going open source with their Visualization engine.

    Here’s a quick blog post of all the new things that you’ll encounter in this new tool.

  • Google Analytics & Power BI – Try it FREE!

    Power BI GA

    If you use Google Analytics to analyze your Web Site Data then you NEED to try the new Power BI connectivity with Google Analytics and its companion Mobile Apps for iOS.

    Our site (and its data) was featured on the official Power BI blog today and here are some screenshots of the traffic data shown inside Power BI thanks to the GA connectivity:

    GA Dashboard

    GA Dashboard

    GA Report

    GA Report

    Follow this link in order to get a full walkthrough of how to set this up.  Completely FREE!

    Check Blog Post
  • What is Power BI?

    This information is based on the features and services that Power BI offers as of January 2015.

    Microsoft Power BI

    • Excel Add-ins

    • Cloud Service

    • REST API

    What is Power BI?