Miguel Escobar

Author Archives

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

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

  • Power BI Custom Connector Languages


    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?


    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.

  • Roundup: New Custom Connectors and Videos

    For the past 8 weeks I’ve been focusing in exploring the Power Query Extensibility Model (PBI Custom Connectors) and I can confirm that the possibilities with this extensibility model are incredible.

    About Custom Connectors

    Custom Connectors not only bring new functionality to Power Query (like new data sources), but it also extends current functionality and can make some HUGE quality of life changes and improvements to the way we work with Power Query by creating custom connectors that act as function libraries.