Have you wondered how to find the native query in Power BI Desktop after query folding does its thing? Do you even know what query folding is? In this video, Adam looks at how you can find the query t…

Power BI offers fantastic ways to integrate data from online sources. Often it is useful to just use parts of that and extend your own data set with details from an online API. Here -as promised in ou…

Have you ever wanted to do a specific type of filter that might be too specific to do with just the regular filter drop down or even the advanced filter operations in Excel? Let me give you an example…

In Chapter 12 of my book, I published detailed walkthroughs that cover Translator Text API, Sentiment Analysis, Key Phrase extraction and the detection of languages using Cognitive Services. As I worked on the chapter, I realized that it will make sense to prepare a template that will streamline all of these APIs together to address …

Have you ever wanted to extract information from a web page to use in a calculation, or as a reference for something else you may be working on? I have Use Microsoft Excel Power Query to scrape engine…

I am often working on datasets where there is more than one condition for a conditional column. And whilst the GUI based Conditional column is really good, it currently does not have the capability…

Split text by any (whole) string instead of any character (within a string) like in the native Text.SplitAny -function with my modified new version:

A few days ago, I ran into a problem where I had to filter a product table by a dynamically set of columns – so in one solution it could be filtering by field x and y and in other solutions it had …

Have you ever had a similar situation where a REALLY important value of the file is in either a header or a footer section? Let me give you a clear example with the following file: In the image above you’ll see that we have 2 values in the header section right under the logo of …

The more advanced Power Query/M developers among you will know about query folding, the way that the Power Query engine pushes as much of the heavy-lifting of a query back to a data source. You may al…

One of the most common issues faced when calling web services in M is that the the easiest way of doing so – creating a function that calls the web service, then calling the function once per row in a…

The big news in the world of Excel right now is the introduction of dynamic arrays. They’re only available in the Office 365 click-to-run version of Excel and, at the time of writing, only available t…

This is the second bonus exercise for my book, Collect, Combine, and Transform Data using Power Query in Excel and Power BI. In Chapter 5 you learn how to preserve context from titles in the worksheet grid after you combine multiple tables, as illustrated here: In this bonus exercise you will learn how to address a …

This is the first post of a series of bonus exercises that will accompany my book, Collect, Combine, and Transform Data using Power Query in Excel and Power BI. The topics of these bonus exercises wil…

The M-Language for Power Query and Power BI is a functional language that is foreign to the classic loop constructions of other languages. Instead, functional programming languages use recursion to repeat expressions. In the first part of this series I show you how to implement recursive functions in M and we take a look at advantages, disadvantages and alternatives of recursive functions.

With the recent announcement of dataflows in the Power BI Service, I see more people will be looking to better understand and leverage dataflows by using the M language which is available in Power …

The idea of creating a Power BI custom connector for the Power BI REST API is not a new one: Miguel Escobar wrote one earlier this year (see here for the source code and documentation). However the Po…

See a transparent history of changes to Power Query M functions by using GitHub.

While the native function Text.BetweenDelimiters will only return one occurrence of a string found between 2 delimiters, my new function will fetch all of them.

In this video, we look at the new fuzzy match / merging option within the October 2018 version of Power BI Desktop. This feature is incredibly powerful when it comes to data matching and can be adjusted to meet your needs.

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 …