What is Reddit's opinion of Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016?

From 3.5 billion Reddit comments

Highly recommend Power Pivot and Power BI by Rob Collie and Avi Singh. It helped me a lot with understanding both data models and DAX.

They use AdventureWorks DB so you can practice as you go.

Power Query and Power Pivot will make you a stronger analyst than 90% out there (that primarily use excel at least)

Learning how to model data in Power Pivot and basic DAX functionality will make your pivot table and chart game on another level. It would also transfer to Power BI.

No more of that formula outside of an Intermediate pivot table shit.

Highly recommend this book as it changed the way I use Excel for the betters day forever and while I’ve always been the go-to excel guy (on to more powerful tools now), it blows people’s minds all the time.

https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395/ref=nodl_

Prior knowledge of SQL will make you even more powerful in PQ/Excel. I’ll give you an example.

Your business has data stored in different places; some of the data you need is stored in your data warehouse and some of it is stored in an Xlsx file on Sharepoint or network/cloud file system.

An average analyst might ask someone to pull the data from the data warehouse for them and then paste the datasets into a worksheet, and then maybe vlookup/ index/match / sumifs to merge the data together. This might work for a quick and dirty but is not scalable and is prone to errors.

With Power Query and SQL, you can connect directly to the data warehouse and the excel file separately, pull the data you need from each, join/append/transform/filter the data sets or whatever you need to do. This all happens before you’ve loaded a single point of data to excel and all of your steps are recorded.

You can then load this data either directly to a table and/or load it to the data model (power pivot). From Power Pivot you can define custom measures using DAX that allows you to make completely custom pivot tables and charts without intermediate tables. It also allows you to create a data model (look up star schema) that is extremely powerful.

Here’s the kicker. Once the pivot table or chart has been created from your data model, all you need to do the next time you want fresh data is refresh the pivot and all the steps and logic you setup will run automatically and update your report. This basic flow will blow peoples minds, which is crazy because it’s really not very complicated.

I simplified this and there is a lot to learn within everything I said, but its not rocket science and is very powerful.

This book helped me immensely in the beginning and set the foundations for my continued learning and career as a business intelligence developer.

Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016 https://www.amazon.com/dp/1615470395/ref=cm_sw_r_awdo_Z3CNKGRRKKSG6SAE7SYR

Switch true is just a fancy way to write nested if statements. If you just want "What is the difference in date between these values?" then you can just use datediff.

Dax and m are both based on functions. Functions have zero or more parameters and return a value. Braces [ ] usually represent an array of data or in Dax, they represent a column of data. Ex [1,2,3,4] Brackets { } usually represent key:value objects ex: {“value”: 1, “something else”: 2}.

M is a very different from Dax. It’s important to note that after the power query stage, data is stored in memory in a columnar format. So column ‘a’ is a vector of data in the format of that column (integer, float, w/e). This is a very efficient storage method to run fast real time calculations and Dax operates on the data in this columnar way. This requires you to think about it differently than querying a sql database or writing formulas in excel. Measures will also be effected by contractual filters. If you throw data into a table, the engine is basically filtering the data by each category in your table and then applying the measure logic.

M on the other hand, does not work like this. It is a row by row way of applying changes and functions to the data. It’s much slower, but more similar to sql.

This book has a lot of simple concepts and patterns: https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395/ref=mp_s_a_1_1?dchild=1&keywords=power+pivot+rob+collie&qid=1615398200&sprefix=power+pivot+rob+coll&sr=8-1

More info: https://docs.microsoft.com/en-us/dax/dax-syntax-reference

I took a live power bi class with the author of this book. He's amazing. I refer to his book from time to time with specific questions when I build dashboards. One of these days, I should go through all the pages of this book - I'm sure I'll pick up more tips along the way.

Realize that what you'll mainly study is the engine of PowerBI dashboards, which is driven by DAX/PowerQuery.

Here's some content in the book, which I learned in 2 days (16 hrs):

It includes lessons on the difference between calculated columns and measures; how formulas can be reused across reports of completely different shapes; how to merge disjointed sets of data into unified reports; how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not; and how to create time-intelligent calculations in pivot tables such as “Year over Year” and “Moving Averages” whether they use a standard, fiscal, or a complete custom calendar.

​

If your work would sponsor, take the P3 live training class. You'll learn much faster with an instructor!

>Rob Collie and Avi Singh'

https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395

this one?

I bought a ton of books off of Humblebumble before and bought the video tutorial of Udemy (https://www.udemy.com/course/microsoft-power-bi-up-running-with-power-bi-desktop/learn/lecture/10087678?start=15#overview) but my biggest fear is that Power BI evolves rather quickly compared to other software. Will the books/tutorials be up to date enough to use them?