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, 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!

For more information or to learn more about analyzing data, contact 3Cloud today!