Part 2: Power BI and Google Sheets – Structuring the data

In this simple example we will use a number of fact tables where we enter the results for each particluar date, and a dimension table for the Time dimension. The fact tables will be bound together through a Date key, which Power BI uses to understand how data is connected. To start with we have to choose what we want to measure, over what dimensions and granularity, that is; What is the lowest level on which we wish to distinguish data?

In our case we want to measure habits (facts) over time (dimension), and we choose the lowest granularity to be One day. Notice that we could also choose a lower level such as hour, which would make it possible to give us a very detailed view of our life (but be a pain in the *** to keep updated!).

The fact table will be structured in the same way as when doing pivot tables in Excel: Fields arranged as columns, and the facts for one particular date make up one row. Here’s how it looks:

Separate your facts into several sheets to improve usability

In Power BI, all the fields in one table will end up in one big “folder” in the interface. As of this writing Power BI doesn’t offer a way to categorize fields into folders. That is why you should separate the facts into several sheets. In this case we have separated the data in categories “Exercise”, “Weight”, “Health” and “My day”.  In Power BI these tables will show up on in the Fields-section on the right, and the fields separated by table – which is exactly what we want! To the right is an example of how it might look:

You will need a time dimension table

A “Time dimension table” is basically a table that gives lots of information about each date that will come in handy. There are obvious things such as Weekday, Year, Month and Weeknumber, but also less obvious things such as “Number of days back in time” which gives us the possibility to create filters such as “The last 30 days”.

Of course, these calculations can be done directly in Power BI's interface, but it is generally a better idea to put them in the data store (= the tables) partly because it provides faster runs. This is what our time dimension table looks like:

Some handy time Google Sheets formulas

These are formulas that are useful when creating filters such as “Show data from 30 days ago until today” or “Show me the last 6 months”. In the formulas below – change [DATE CURRENT ROW] for the cell where the date is placed.

• Days back in time = INT (TODAY () - [DATE CURRENT ROW])
• Number of months back in time = - (((12 * YEAR ([DATE CURRENT ROW])) + MONTH ([DATE CURRENT ROW])) - ((12 * YEAR (TODAY ())) + MONTH (TODAY ()) ))
• Number of weeks back in time = - (INT (([DATE CURRENT ROW] - (TODAY () - WEEKDAY (TODAY (), 2))) / 7))

Continue to Part 3: Connecting Power BI to Google Sheets