Question: Resources for Learning PowerBI

[AaronJTrent]

Hi all, I recently posted this question on Twitter and @taras recommend I ask here.

I've been tasked with setting up some dashboards for work and the environment will be PowerBI. SQL and R are my comfort zone and I am looking for advice or resources to best translate that to effectively using PowerBI.

[taras]

The way I read your question on Twitter was with Power BI in relation to R. That is, since it is an R community, most (all) questions here are related to R this way or another.
Having said that, let me try to answer your question with the take of translating R skills to Power BI.

[taras]

I guess the first thing to highlight is that Power BI is obviously a BI platform with GUI, not a programming IDE. It is a far cry from RStudio environment, if that is what you're used to.

Nevertheless, the UX of Power BI kind of sets you up to take your data on a typical analysis journey: from raw state to final result, which is most of the time is an interactive set of charts.

There are 2 distinct modules in Power BI Desktop that you need to be aware of:

• The Query Editor, aka Power Query. This is where you prep and tidy your data. The experience in this module will mostly map to your R experience, as it is basically a GUI to program a set of data transformation steps (it writes some code in its own M language behind the scenes). Read more here: https://taraskaduk.com/2018/03/29/power-query/
• The actual Power BI desktop part / designer / whatever you want to call it. This is where you connect your data tables together, create "measures" and build charts. These measures look a lot like Excel formulas, and are written in DAX (if you've dealt with cubes, SSAS and all that fun stuff, you'll recognize this mess). This part doesn't map to R (maybe a bit to one's Shiny experience, but no, not really). The important things here to realize is that one is advised to break away from traditional EDA steps in SQL/R like:
  • Joining tables. You don't need to join data tables of different grains, you're better off keeping tables separately in there third (third-is) normal form, and connecting them together. Power BI will utilize the table relationships and will call the data from related tables when needed.
  • Grouping by / summarizing. I always suggest bringing the data in at its grain (unless constrained by data's size) and allow Power BI do aggregations via its measures: basically, functions on data that are called and calculated based on context.

Basically, your first order of business is to make your data as tidy as possible in the Query Editor and get a set of tables that spark joy, and then in the main designer module you connect the tables and write some aggregation measures (sums, averages, counts). And then you throw these things on charts.

I always suggest to spend more time in Query Editor cleaning the data: it is a more intuitive process for someone who is used to code their analysis. DAX measures, on the other hand, are chaotic, and there is too much to learn to justify the time. The cleaner your data - the less DAX functions you need to get the charts that you want.

As a side note, you can use R scripts and SQL queries as your means of data import. You can also use R code to create your own visuals, but I'd say it is a niche case, as built-in Power BI visuals have a better UX from interactivity standpoint.

There is a lot to digest, I've been using Power BI since its first public release in 2014, and I still have a lot to learn.

The book that nicely outlines the infrastructure of Power BI that helped me is Power Pivot and Power BI by Rob Collie & Avi Singh: https://powerpivotpro.com/the-book/ I found this one to be helpful many years ago, but I'm sure there are more resources available now.

[taras]

Also, I gave a talk on R in Power BI last year at a local SQL Saturday event. My slides suck for someone to follow along after the fact (and some links may not work any more ), but FWIW, here are the slides: https://taraskaduk.github.io/sqlsat759-pbi/presentation/slides.html#1

[AaronJTrent]

Thanks for taking the time to write this up for me. Getting used to the GUI instead of an IDE has been weird and I appreciate the tips on how to interact with the modules.