{"product":{"product_id":"1615470395","title":"Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI \u0026 Power Pivot in Excel 2010-2016","price":"6.2","image_url":"https://m.media-amazon.com/images/I/513QjFBYGFL._SL500_.jpg","url":"https://www.amazon.com/dp/1615470395"},"comments":[{"body":"Highly recommend [Power Pivot and Power BI](https://www.amazon.com/dp/1615470395/ref=cm_sw_r_cp_api_i_X9YeFb9MF7Z8D) by Rob Collie and Avi Singh.  It helped me a lot with understanding both data models and DAX.\r\n\r\nThey use AdventureWorks DB so you can practice as you go.","subreddit_name":"PowerBI","author":"TruthWillMessYouP"},{"body":"Power Query and Power Pivot will make you a stronger analyst than 90% out there (that primarily use excel at least)\r\n\r\nLearning 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. \r\n\r\nNo more of that formula outside of an Intermediate pivot table shit. \r\n\r\nHighly 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.\r\n\r\nhttps://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395/ref=nodl_","subreddit_name":"excel","author":"TruthWillMessYouP"},{"body":"Prior knowledge of SQL will make you even more powerful in PQ/Excel.  I’ll give you an example.\r\n\r\nYour 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.\r\n\r\nAn 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.\r\n\r\nWith 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.  \r\n\r\nYou 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.\r\n\r\nHere’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.\r\n\r\nI simplified this and there is a lot to learn within everything I said, but its not rocket science and is very powerful.\r\n\r\nThis book helped me immensely in the beginning and set the foundations for my continued learning and career as a business intelligence developer. \r\n\r\nPower Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI \u0026amp; Power Pivot in Excel 2010-2016 https://www.amazon.com/dp/1615470395/ref=cm_sw_r_awdo_Z3CNKGRRKKSG6SAE7SYR","subreddit_name":"dataanalysis","author":"OKMrRobot"},{"body":"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. \r\n\r\nDax 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}. \r\n\r\nM 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. \r\n\r\nM 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. \r\n\r\nThis 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\u0026amp;keywords=power+pivot+rob+collie\u0026amp;qid=1615398200\u0026amp;sprefix=power+pivot+rob+coll\u0026amp;sr=8-1\r\n\r\nMore info: https://docs.microsoft.com/en-us/dax/dax-syntax-reference","subreddit_name":"BusinessIntelligence","author":"bodhi_mind"},{"body":"I took a live power bi class with the author of [this book](https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395). 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.\r\n\r\nRealize that what you'll mainly study is the engine of PowerBI dashboards, which is driven by DAX/PowerQuery. \r\n\r\nHere's some content in the book, which I learned in 2 days (16 hrs):\r\n\r\n*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.* \r\n\r\n\u0026amp;#x200B;\r\n\r\nIf your work would sponsor, take the [P3](https://powerpivotpro.com/training/) live training class. You'll learn much faster with an instructor!","subreddit_name":"PowerBI","author":"summer3131"},{"body":"\u0026gt;Rob Collie and Avi Singh'\r\n\r\n[https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395](https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395)\r\n\r\nthis one?\r\n\r\nI 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](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?","subreddit_name":"PowerBI","author":"imthedan"}]}