BUSINESS INSIGHTS

Jan 19, 2017

Demo Day: Deriving Dimensions in Power Query

Merrill Aldrich Posted by Merrill Aldrich

In today's self-serve Bl world, it's common to need to analyze or mash up data from a huge variety of sources. Sometimes data arrives in a nice, clean and structured form, but it may also be delivered in a very basic flat file extract or single database tables. Power Query (also known as Get and Transform in Excel 2016) is a great way to shape data into a clear and more convenient form for analysis.

iStock-508901890edited.png

You may have to take a flat file data extract, perhaps pulled from some source database or a public data set, and shape it into a small star composed of a fact table surrounded by related dimension tables. In a Demo Day video below, I've taken a slice of Microsoft's new World Wide Importers SQL Server 2016 sample data and made a mock data extract, stored in a single CSV file, to emulate this scenario.

Making Dimensions and Facts

After downloading the sample data or finding a data sample of your own, follow along in Power BI with the short video below to learn how to:

  1. Import your original data and identify columns to make into dimensions.
  2. For each of those dimensions, make a copy of the table that isolates the distinct values from the column(s), and numbers them with Add Index.
  3. Join the original data to the dimensions using the text columns for matching.
  4. Expand the matching ID values, and remove the corresponding text columns, to make a neat, compact fact table.
  5. Analyze!
 
 
 
 
 
15:38
 
 
 
15:38
 
 
 
 
 
 
 
 
 
 
Wistia video thumbnail - Deriving Dimensions in Power Query
 
 

Thanks for reporting a problem. We'll attach technical data about this session to help us figure out the issue. Which of these best describes the problem?

Any other details or context?

Cancel
message
 
 
 
 
 
 
 


For more information or to learn more about analyzing data, contact BlueGranite today! We offer onsite, hands-on, instructor-led training to help business and IT teams take full advantage of self-service BI and analytics.

New Call-to-action
Merrill Aldrich

About The Author

Merrill Aldrich

For over ten years, Merrill has helped organizations all across the US integrate and understand their data. He has blended backgrounds in Business Intelligence, ETL Development, Database Administration, Building Architecture, 3D Modeling and Animation, and Swedish.