Tag Archives: power query

  • First Impression on Power BI Incremental Refresh


    Power BI just recently released the ability to set up Incremental refresh policies through the Power BI Desktop.

    This is a short blog post with my first thoughts on it.

    Everybody has been excited about the possibility of doing Incremental Refresh through Power BI. Back in the day, this was only something that you could accomplish using Partitions in SSAS, which would require a server and it didn’t use M syntax at all.

    Recently there have been new releases like Azure Analysis Services and new versions of Analysis Services that have Power Query integrated into it which allow for really dynamic M syntax. To this point, everything was through a SSAS but now we have the ability to basically create partitions through a Power BI Desktop model.

    I not only wanted to test this out by itself, but by combining it with my Custom Connectors. Could I create a scenario where I’m getting data from the WooCommerce API (using my Custom Connector from here) and set up an incremental refresh?

    Let’s find out.

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


    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


    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


    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


    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)


    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



    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.

  • New Introductory Udemy Course + Job Opportunity

    Blog Post

    Ken and myself just released a new Introductory video course on Udemy!

    This course is basically an introduction to the potential of Power Query and the videos are taken directly from our full Power Query Academy, so you can rest assured that they are of high quality and high production value.

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


    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


    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:

    up Appointments Needed
    (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:              

    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


    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