power query

By Matt Allington The concept of parameter tables in Power Query will be familiar to some and new to others.  A parameter table is a way that you can “pass

When you’re writing an M expression for a custom column in Power BI or Power Query it’s easy to reference the values in other columns. A slightly more difficult question, however, is how can you re…

There are a lot of cool new features in the September 2016 update for Power BI, so many in fact that several of the really important changes in the Query Editor are in danger of going unnoticed. In…

in Power BI Desktop , PowerQuery Excel

One slightly frustrating feature of Power BI is that some of the cool stuff you can do in M code for loading data, and which works in Power BI Desktop (and in Power Query), causes errors when you t…

Part 1 describes approach showing how to work with List.Generate, Table.FromRecords, List CrossJoin in Get & Transform (aka Power Query, M language). Homework is done. Advice is digested. And I…

Recently I faced interesting Power Query problem. Actually, initial problem has nothing related to Power Query. I required to export quite big volume of data from SAP BW using Business Objects Anal…

In this post I consider two types of hierarchies First table defines typical hierarchy of companies. Usually, such hierarchy is used for financial reports to group key figures. Most probably, we kn…

The July 2016 update for Power BI Desktop included the ability to make parameters data-driven, by giving the option to bind the Suggested Values (previously called the Allowed Values) property of a…

There are many ways to get a value from parameters table and then pass it to query. One of this methods uses direct selection (quick filter) of unique parameter name.

Matt Masson and Theresa Palmer gave an excellent presentation on Power Query and M for the PASS DW/BI Virtual Chapter a few days ago (hopefully it will be on their YouTube channel soon). One thing …

A few weeks ago I wrote a post about comparing the contents of entire tables in Power Query, and a question came up in the comments about how you might go about comparing values in columns rather t…

It’s a very common requirement when you’re building a report in PowerPivot (or indeed in any BI tool) for it to automatically show data for today’s date, the current week or month (plus maybe a set…

power

Here

SQLBits Session - See how to use Power Query to build a detail-level reporting solution with Chris Webb

How slow is a bit slow? How many rows are there in each table, and what type of data source are you using? I was wondering if a more 'relational' appr

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 …