Power Query: Load to Data Model for Cleansing and Analysis

One option when loading a query from Power Query is: Load to Data Model (or create Relationships). In this video that’s what we cover, and why.

In this video we have 3 tables of data and we’d like to answer questions that can only be answered if the data were all in one place.

We could use Power Query but Power Query would require a bunch of queries and isn’t good if we have ad hoc questions. Instead, loading to a Data Model (Relationships) in Power Pivot is the sexy thing to do!

The Data Model will treat the 3 separate tables like a single source of data that we can create one pivot table from.

One thing that’s important with Data Models and relationships. One side of the relationship needs to have all unique values. Example:

We can have Routes: TU-3 and WE-1 multiple times in a table, but we can only match that with a table that has all routes only one time each. More formally: we can have one-to-one relationships and one-to-many. We cannot have many-to-many relationships.

#DataModel
#PivotTable
#SexyData

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2