Power Query

This is a quick tip on automated testing with Power Query. I loved Nar's post on Automated Testing using DAX. I especially like the rule of always including controls so that business readers can share responsibility for data quality. For my part, I sometimes use hidden pages in Power BI reports to assure myself of

If you are following me on social media, this video will not be new. Two days ago I received my print copy of the book. The book is real 🙂 I was so excited about it, so I had to fool around and create this video. A journey of one year has ended. I would …

The older I get, the more consistently I encounter things that make me feel old. And it’s not just the usual things, like those damned kids on my lawn. It’s things like technology. And …

You can easily change a column type by it's relative position. This requires some basic editing of the M-Code and using the Table.ColumnNames formula.

Case When data is not extracted from a cleaned up or validated environment, such as a Data Warehouse or Data Mart, the first step is to ana...

I try to avoid using Excel as a data source when I can. Although Excel has many benefits[1], its limitations[2] make it less than ideal for enterprise BI solutions. Despite this, it feels like I end u…

For a while now I’ve had an idea stuck in my head: wouldn’t it be cool to build a Power BI solution where a user could enter data into an Excel workbook and then, as soon as they had done so, they …

A quick update: The print version of the book will be released in 4 days! It may take few extra days to reach the major distribution centers. On Amazon there are some data sync glitches right now, so …

Power Query is amazing for cleaning, shaping and analyzing numerical data, buut it can also help with analyzing text that is unstructured.

We are back to finish our discussion on the visualizations for Comparing Rows in Power Query! How to visualize the prices and daily changes The two metals have very different price ranges so it is difficult to see them in one chart. The daily changes are relative so they can be used to compare the price behavior

Today Microsoft released the October 2018 update of Power BI Desktop. One of the fabulous new features that has been released is the ability to get a visual profile of your data in Power Query BEF…

You know it and I know it. Data quality is a BIG problem that reduces the business value of BI. ETL practitioners will probably recall that SSIS includes a comprehensive Data Profiling Task but it is …

Recently I picked up an interesting request to transform the words in a column to proper case, but keep certain keywords like in a defined list. So let's transform text with exceptions:

Certain M functions accept a Culture parameter that ensures the result is returned in the defined language, regardless of the language of the OS.

As a heavy R and Tidyverse user, I’ve been playing with Microsofts m/Power Query language included in Excel and PowerBI from that perspective, looking for the functions to make my life easier, developing small code pipelines for my processing and trying to get a smooth, clear and maintainable data manipulation process in place. The Problem In PowerBI I have data generated from an API call to HubSpot, which deliveres a json which is flattened as the first step of the process into a table with hundreds of columns.

This is exciting! The digital version of the book “Collect, Combine and Transform Data Using Power Query in Excel and Power BI” is out. You can purchase it on Amazon here or on Microsoft Press Store here. Note: The book includes 70+ step-by-step exercises with many sample files that are key components of your learning experience …

The short answer: no. The long answer: It is, technically, possible to use Power Query (in either Power BI or Excel) to write data to a data source; for example, here’s an old post I wrote showing …

There are two kinds of nuthin' in Power Query: null and blank. Why is this important? Because null and blank are not equal!

This is going to sound obscure, and it is, but recently I’ve been using the #table() intrinsic function in M a lot – specifically the version that takes a table type as its first parameter (as I descr…

It is straightforward to compare values between each row in an Excel table and the next row. You can create a calculated column in a table and reference values in other columns in the same row by name and cells in different rows by using regular referencing. Such a reference is fragile. Sorting or deleting rows will

This is a very short post! A lot of people have blogged about how to convert numbers between different bases in M (see for example Maxim Zelensky’s very elegant solution for converting from binary …

When you work with tables and queries in Power Query and Power BI, you get the option to copy them through these actions: Duplicate, or Reference. It has been always a question in my sessions and cour…

I was super-excited when, a few days ago, the long-awaited integration of Power Query into Microsoft Flow was announced. I was then gutted when work commitments meant I couldn’t blog about it right…

We have been working on a set of helpful Power Query Recipes for Excel and Power BI and are excited to announce that they are now officially available!

In the Microsoft Flow October 2018 update - link – we got a very interesting feature added to Microsoft Flow – Power Query and as they state – Also, to better empower our users – we have integrated Po…