When it comes to developing a Power BI report, we can use it for accessing data, transforming the data, and turning the data into information using visuals. With no doubt Power BI can do the job from beginning to end. Now, this is not the only, nor necessarily the best, tool for all the steps of a report. At different stages, you might ask, Power Query or SQL Server? The big questions are which one to use, when, and why?
In this article I want to compare four scenarios using Power Query and SQL Server. We'll go from only using Power Query to the manipulating data entirely in SQL Server. Let’s see which Microsoft tool I would use depending on the conditions.
First Scenario: Accessing raw data directly using only Power Query
Here Power Query does the entire job. We are going to access the data as it is. Once in the editor we will add or remove columns, split or concatenate fields, replace values and/or add new ones, and so on, until we have a dataset shaped as we desire.
Introduction Often at times, you may come across situations where you need to calculate a column repeatedly multiple times in the same Power BI report or across multiple reports. Although you can use the calculated columns to some extent, these are not robust and not reusable. In order to reuse the same piece of code, […]
Problem A few weeks back, I got a request from a client of mine who had a very special requirement. They wanted to display values for a specific period in the past based on the selected dates in the Power BI report. Let us assume that if we select 30 June 2020, the report has to […]
Introduction Reporting on web data has been a fascinating part of developing any dashboard or reports in Power BI. Often this data is available either free that can be consumed anonymously or by using some kind of authentication mechanism. In this article, I'm going to demonstrate how to connect to a web application from Power […]