Does anyone else experience that refreshing on-premise data via a gateway (for example amount of files in a folder) works if you hardcode the path to the folder, but when you create a function where the path is the input parameter powerbi says that you are not able to refresh this data source?

I have used a lot of time in the Power Query Formula specification help on the web – and several time for the same function – so I ended up switching between browser and Power BI Desktop. Then I re…

Resources to help you figure out what Power Query functions exist, especially since they are often different than what we’re used to in Excel.

Why scenario of “Loading Power Query function from GitHub” is interesting? Assume that you work in big company, or small, doesn’t matter. You and your colleagues use shared librar…

In order to share M-code that will instantly run on other computers, you should pass on the data as well. See how this will be generated automatically with M

Get ready to be amazed 🙂 This is the fourth post in the series The Definitive Guide to Unpivot with Power Query in Excel. In this series we walk you through one of the coolest data transformation features in Excel – The Unpivot transformation. Powered by Power Query technology, the Unpivot transformation is available for everyone using the new Get …

In my last post you saw how to join two tables together using conditions other than the built-in inner, outer and anti join conditions. However, as I mentioned, you need to be aware of some of the …

Following a reader’s request, today we will unleash the power of List.Accumulate. The official documentation on List.Accumulate here was very confusing for me: [crayon-59aec6d1333d2775222960/] Argument Description list The List to check. seed The initial value seed. accumulator The value accumulator function. OK. I understood the list argument quite right, but the other two arguments were post-nuclear-bomb science fiction. So let’s …

Yesterday, Microsoft released the June 2016 Power Query update.  Even though there are only four items on the list of new features, some of them are quite impactful. What’s new in the June 2016 Power Query Update The four new … Continue reading →

Last week’s post on allocation in Power Query caused quite a lot of interest, so I thought I would follow it up with a post that deals with a slightly more advanced (and more realistic) scenario: w…

When looking through the Power Query library reference I noticed that the Table.Group() function has a very useful option to aggregate data using what it calls local groups. Rather than explain wha…

One of the things that has intrigued me in the Power Query docs for a while now is the Expression.Evaluate() function, which takes some text and evaluates it as a Power Query expression (rather lik…

If you’re interested in performance-tuning a Power Query query, you’re going to need a reliable way of telling how long your query takes to run. It’s not as easy as it seems to adapt your query to …

Click Here to download this Tutorial file Very often our analysis starts with a flat data set that contains all of the pertinent columns in a single table that looks like the one above. As we can s…

I've been using Microsoft's Power BI to help a wonderful client of mine build self-service BI for their analysts and away from a system wher...

Data Analysis Expressions (DAX), originally the formula language for PowerPivot workbooks, can also be used within the MDX query window of SSMS to directly access data from a tabular SSAS database, an in-memory database that uses the xVelocity analytics engine and compression. Robert Sheldon shows how easy it is to retrieve data from a tabular database.

Today’s post isn’t of a technical nature, although it does have a pretty long technical background… I’m now at the point where I need beta testers for a new add-in I’m planning to release. What Does the Add-in Do? In … Continue reading →

After my post earlier this week on creating current day/week/month/year reports in Power BI a few people asked me for a more detailed explanation of the way I was creating tables without using a da…

One very common requirement when creating a Power BI report is the ability to apply a filter for the current day, week, month, quarter or year. There are several ways of implementing this: you coul…

I saw an interesting post the other day on the Power Query Technet forum which showed how the List.Buffer() function could be used to improve calculation performance. This is something I’d seen hin…

In a year’s time there will be a great opportunity for someone to present a session on “Power BI Development Best Practices” at the PASS Summit. Before then, we will all have to work out what those…

There are three options:   There are two ways to write a measure that returns the most recent date / processing date in your data model.   1. If you have a data(transaction) table Most Recent Transaction = CALCULATE( MAX(Transactions[Date]), ALL(Transactions) ) 2. If you have a calendar table - ther...

While I was at the PASS BA conference in San Jose, CA last week, I got an email from a reader asking if Power Query could create a Cartesian Product join. Now I’m an Excel guy, and I’d never heard … Continue reading →

Technical articles, content and resources for IT Professionals working in Microsoft technologies

In this article we will look at multi condition logic in Power Query, exploring a scenario where we need to choose between several options.

This article shows you how to create dynamic data sources using Power BI query parameters. You also learn how to use query parameters in Filter Rows.