power query

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…

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

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.

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 …

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 …

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

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.

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.

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

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…

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

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…

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 …

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…

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

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 …

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 …

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:

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…

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…

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

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

A common problem I see some people struggle with in Power BI is when a slicer contains a (Blank) record for some reason. The cause of the problem is not obvious and hence it is not clear how to fi…

In a comment on my blog post about international date and number formats and changing data types with the “using locale” option in Power Query/Power BI, Jan Karel Pieterse asked if there was any way t…