Blog
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 […]
Read full article >
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. Surprise! You can now refresh custom connectors through the Power BI personal gateway (requires April 2018 version) https://t.co/SUF4CEuQBA#pqsdk — Matt Masson (@mattmasson) April 22, 2018
Read full article >
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 goal […]
Read full article >
The Navigation Step in Power Query and Power BI – Navigating to rows, columns and cells
What is a “Navigation” step in Power Query? 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 […]
Read full article >
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: Imke’s way Bulk Replace The reality is that there are multiple ways to do it, but […]
Read full article >
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. Types of Errors Power Query primarily handles 2 types or categories of […]
Read full article >
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 […]
Read full article >
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 […]
Read full article >
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 […]
Read full article >
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 […]
Read full article >
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 […]
Read full article >
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 […]
Read full article >