Power BI

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.

pquery - A collection of functions for use in Excel Power Query

"Do we use Power Pivot or Power Query?" – I get this question all the time when teaching Power BI classes. (Note: This applies in Excel as well as Power BI

The April 2016 Power Query Update was just released for Office 365 subscribers, and I can confirm that it is available to the First Release customers, as I’ve already got it installed.  (If you’re on a later branch it may … Continue reading →

Better way to use parameter tables in Power Query and Power BI: Easier to use and futureproof/robust. Transpose the table and your parameter names equal the

I got a question on the blog recently about creating a banding function in Power Query, or creating buckets for Accounts Receivable transactions.  (30-60 days, 60-90 days, etc..)  As this is something that can be applied to a lot of … Continue reading →

Power BI designer is based upon the Power Query formula language informally known as

A great question come up on the Power Query forum: “I would like to know if there is a way to pass parameters to a Stored Procedure used within a Power Query.” Here are the steps in det…

If there’s one feature of Power Query that’s guaranteed to get Excel users very, very excited indeed it’s the ability to combine data from multiple workbooks into a single table. The bad news is th…

letParamSource = GAIDs[Content],RaramRow = Table.SelectRows(ParamSource,each([Parameter]=ParameterName)),Value=if Table.isEmpty(ParamRow)=truethen nullelse Record.Field(ParamRow{0},"Value"),inValueletSolutionPath = fnGetParameter("File Path"), Source = Folder.Files(SolutionPath & "Data"),#"Combined Binaries" = Binary.Combine(Source[Content]),#"Imported CSV" = Csv.Document(#"Combined Binaries",null,",",null,1252),#"First Row as Header" = Table.PromoteHeaders(#"Imported CSV")in#"First Row as Header"

If you've been using Power BI or you've seen the demos, then like me, you're probably excited about Power Query. As a data nerd, I've been having a lot of

Power Query transformations can be very powerful, but they only work on one data source at a time. Sometimes data providers will only provide their data in discrete chunks, like one category per ta…

Getting Started and Understanding Power Query and its M Language

By Julie Smith I was happy to co-present a session at this week’s Atlanta BI User Group with Rob Volk (@SQL_R) meeting entitled “Harvesting XML Data from the Web with Power Query and Cu…