Power Query

  • The Best ETL tool for the Business Analyst–Tableau Prep vs Power BI vs Trifacta Wrangler (Google Prep)

    image

    I’ve been working with Power Query inside of Excel and Power BI for the past few years, but I’ve always tried to stay on top of its competitors, trying to make sure that I’m investing my time using the best tool that there is.

    This is one of those times where I allocate some time to find out if Power Query is still the best ETL tool for the Business Analyst out there.

    In this case I’m going to compare 3 tools:

    • Power Query inside of Power BI Desktop and representing Microsoft
    • Tableau Prep pka as Project Maestro ad representing Tableau
    • Trifacta Wrangler – representing both Trifacta and Google’s Data Prep (since for all intends and purposes, it provides the same UX for the end user)

    Disclaimer: I am in no way being sponsored nor promoted by any of these companies. I’m not a Microsoft MVP nor a Tableau Zen Master. My main goal with this comparison is to find the best ETL tool out there and use it.

  • Refreshing a Power BI Custom Connector in the Cloud

    image

    These are GREAT news! Finally we have a way to refresh data from custom connectors on the Power BI service.

  • Reconfiguring a Step in Power Query for Excel and Power BI

    SNAGHTML584a8a

    One of the coolest features of Power Query it’s something that we colloquially call ‘the gear icon’ which is an icon that appears to the right of some steps. Not all steps, but to most of the steps that are automatically created when you click on a button of the Power Query UI.

  • The Navigation Step in Power Query and Power BI – Navigating to rows, columns and cells

    image

    What is a “Navigation” step in Power Query?

    GIF Navigation

    When using Power Query, you might’ve come across a step that reads “Navigation” and is usually automatically created for you by Power Query, but…what does it do?

    Well, if we look at the code that it was created for the Navigation step, it reads:

    = Source{[Item=”SalesTable”,Kind=”Table”]}[Data]

    It has some curly brackets mixed with some square brackets and some nomenclature that seems pretty strange at first. This is one of the ways that Power Query automatically creates a navigation step, but there are other ways to achieve the same result and depending on the situation Power Query might create a different code.

    This is the main reason why I’m writing this post. To document what are the ways that Power Query has in order to Navigate to a specific Column, Row or even a specific Cell.

  • Replacing Values in Power BI / Power Query (HTML code)

    image

    With Power Query inside of Excel or Power BI, you can replace values by using the ‘Replace values’ button, but there are other approaches when you need to replace multiples values at the same time like the following ones:

    The reality is that there are multiple ways to do it, but it often requires creating another Translation table in order to perform the needed replacement or translation.

  • Query Error Auditing in Power Query for Excel and Power BI

    image

     

    As any other programming language, Power Query handles errors in its own unique way and the goal of this blog post is to give you a few hints on how to audit the errors or warnings that Power Query might throw your way.

  • Data Types, Data Conversion and Ascribed Data Types in Power Query and Power BI

    image

    As we saw in a previous post, there are many things that we need to consider when dealing with Excel files since even the extension of an Excel file can dramatically impact your solution and how Power Query interprets the contents of that file.

    In a more broader sense, we also need to take in consideration 3 key elements that apply to every single data that lands inside Power Query :

    • Data Types – every field/column can have a specific Data Type associated to it
      • Data Type Conversion – every field/column can have its members (rows) converted to another Data Type as well as the Data Type of its field/column defined to a different Data Type.
      • Ascribing Data Types – instead of performing a conversion for every member of a field/column, we can simply define that the column should be considered of a specific Data Type without doing any conversion. This is considered ascribing a data type.

    You might’ve heard about Data Type Conversion, as it’s what Power Query automatically does when you click on the “Data Type” dropdown and select a data type for a column, but you can also do what it’s called Ascribing a Data Type, where you can define a new Data Type to a specific field/column without doing a conversion process. This post will showcase how to do this and what are the benefits of doing this.

  • Calculate Days between dates using Power Query / Power BI

    image

    Have you ever wanted to find out how many days you have between 2 dates? perhaps how many Mondays? how many Sundays? perhaps Saturdays and Sundays?

    Well, in this blog post I’ll show you how you can do that with Power Query inside of Excel and/or Power BI and how you can extend this to other scenarios.

  • Recurring dates with offset in Power Query


    Imagine this scenario, you are a Doctor who has multiple appointments with patients on a daily basis. Sometimes, you need to schedule follow up appointments with your patients just to make sure that everything is going according to plan and basically do a check up.

    You record these appointments in a simple spreadsheet like the following:

    Initial
    Appointment
    Patient
    Name
    Follow
    up Appointments Needed
    Frequency
    (every x days)
    11-Jan-18 Audie Livengood 3 14
    28-Oct-17 Curt Gatz 2 7

    Where, for example, the first row is telling me that the patient under the name of ‘Audie Livengood’ had a initial appointment on January 11th of 2018 and needs follow up appointments every 14 days, for a total of 3 follow up appointments needed.

    You want to calculate how your agenda might look like based on the appointments that you’re making and how many follow up appointments you’re needing on average per patient, so you want to use Power Query to calculate a table like the following:              

    Initial
    Appointment
    Patient
    Name
    Follow
    Up Appointments Dates
    1/11/2018 Audie Livengood 1/25/2018
    1/11/2018 Audie Livengood 2/8/2018
    1/11/2018 Audie Livengood 2/22/2018
    1/11/2018 Audie Livengood 3/8/2018
    10/28/2017 Curt Gatz 11/4/2017
    10/28/2017 Curt Gatz 11/11/2017

    Can you do this with Power Query? Yes! you can

  • 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

  • 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.