Best Power BI Books - Sneak into Power BI & get a deep understanding! - DataFlairBut did you know that you can get Power Query to do this for other data sources too? You have to follow some very precise steps to make it happen and even then there are some problems. If you were to load these two tables into the Excel Data Model, you would probably want to create a relationship between the two tables based on the FruitID column. Here are the steps to use Power Query to create the relationship automatically:. What are the problems I talked about then?
Excel 2013 Power BI Tools Part 5 - Creating Data Models in PowerPivot
Best Excel Books & Power BI Books – 2018
The technology is essentially the same across all of these products so I will generically refer to Power Pivot in this article. The Vertipaq engine is what makes Power Pivot both super fast and highly compressed. A Power Pivot database is not the same as a relational database RDB and it does not support all the relationship types that relational databases support. This can be confusing for people that are new to Power Pivot, particularly if they have at least a basic understanding of how databases such as MS Access work. I explain what you need to know to get started with relationships in Power Pivot here. This article is specifically about physical relationships, however there are ways to create virtual relationships using DAX. I am not covering these types of relationships in this article.
Best books on data & visualization
MSPTDA 21: Power Query: Reduce Data Model Size, Transformations to Columnar Database Size
In the last few years, I and Alberto Ferrari assisted many users of Power Pivot and Power BI who wanted to create their reports using these tools and were struggling with getting the desired numbers from their data. The first approach of an Excel user is to look for a function, or a more complex DAX expression, that can implement the calculation required. Several times, before looking for a correct DAX expression, the problem is defining the correct data model, putting data in the right tables, and creating the proper relationships. Several examples are created in Power BI Desktop because it is free and available to anyone , but certain reports are created using pivot tables in Excel. The goal is to teach the concepts, rather than providing formulas to copy and paste. In fact, the reader should try to apply the same ideas to its own data, recognizing the data modeling patterns described in the chapters of the book. We tried to minimize the use of theoretical terms, trying to introduce with very practical examples and design patterns the terminology that is commonly used in data modeling such as fact, dimensions, normalization, denormalization, star schema, snowflake schema, and so on.