power query

When you build a date table in Power Query you might use the functions under Date to add year, month and day And this will give you three steps in your Query But we can do this a bit faster, and yo…

Complete Guide to Lists in Power Query. Learn how to Make Use of the Most Powerful Power Query Functions (And Master Them).

If you’re working in Power BI Desktop you may sometimes find that your visuals error with the message “Couldn’t load the data for this visual. The operation was cancelled because …

A while back, I was working on a project involving getting data from Excel files. The Excel files contain the data in sheets and tables. Getting the data from the tables is easy. However, the data in the sheets have some commentaries on top of the sheet, then the column names and then the data … Continue reading Combining X Number of Rows in Power Query for Power BI, Excel and Power Query Online

Over the past few years I’ve blogged and presented extensively on the subject of Power Query’s data privacy settings (see here for a post with links to all this content). I thought I kn…

List.Generate is an advanced Power Query function that creates lists. It allows you to implement iterations and has similarities with loops.

Learn how to use List.Generate in Power Query to loop through APIs. Repeat API calls until your condition is false. Works with pagination!

Learn about the Universal Data Consolidator in this guest blog post by Sameer Bhide!

This blog post provides a close equivalent of the DAX's SWITCH function in Power Query, which addresses the SWITCH(TRUE... usage.

In my quest to check out every last bit of obscure Power Query functionality, this week I looked into the ContextInfo option on the Sql.Database and Sql.Databases M functions. This option allows yo…

Why Power BI refreshes time out after two/five hours and how to work around this

Sharing the NORM.DIST function in Power Query as a custom function here, as unlike in DAX it hasn't made it into the standard repertoire.

Blogging about Power BI, Power Platform and other things I find interesting.

Simulate a slow data source - making refresh slow - in Power BI

The Black Scholes formula returns the value of European put and call options. See how to calculate this with the M language in Power Query.

Late last year I was spending some vacation days playing with one of my favorite personal data sets[1], and I realized I needed a better date dimension. So I asked for one. A bunch of folks from th…

In the first part of this series I showed how to create a Power BI DirectQuery dataset connected to Log Analytics to do near real-time monitoring of Analysis Services activity; in the second part I …

Log Analytics contains information on the dataset, report and visual that are associated with a DAX query but that information is in the form of IDs rather than names. Getting the IDs for specific …

How can you create your own data types in Excel? Oh yes, you can do it! In this article, we'll learn how you can create data types in Power Query.

QuerySurge’s BI Tester provides connectors for Microsoft Power BI, Tableau, Microstrategy, IBM Cognos, SAP, and Oracle OBIEE.

In my last post I showed how to use Log Analytics data to analyse Power BI query activity. The problem with looking at a long list of queries, though, is that it can be overwhelming and it can be h…

At Excel, customer focus is at the core of what we do. We appreciate your feedback on adding autocomplete to data validation dropdown lists in Excel for Windows. Your Excel Community votes ensured this was one of the next in line to release after smooth scrolling. Thank you for keeping us honest and...

Here is how to manage that you always import all columns from the CSV file with Power Query. That is useful if file gets additional columns.