Miguel Escobar

Author Archives

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

  • 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

    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

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