Part 3: Connecting Power BI to Google Sheets

September 26, 2016

Now it is time to get the data into Power BI!

Step 1: Publish your Google Sheets as a web page

  1. First you must share the document. Choose “Anyone with the link can VIEW”
  2. Then in the menu choose “File” => “Publish to the web”
  3. Choose “Entire document” and “Web page”, click Publish

Copy the URLs for each sheet into f.e. Notepad. For each table (Exercise, Weight, Health, My Day, Time), choose the sheet and copy the link

Step 2: Enter the data into Power BI

Observe that you need to use Power BI Desktop, and NOT the Power BI web interface.

  1. Open Power BI Desktop and create a new file
  2. Click the button “Get data” at the top and choose “Web” as source.
  3. In the dialogue box, choose one of the URLs from Google SheetsGoogle-URL.jpg
  4. In the data source dialogue box, choose the Google Sheets URL as a data source
  5. In the “Navigator”-popup, notice that the data lies in “Table 0”. Check the table and click the “Load”-button.
  6. Table-0.jpg
  7. The fields pane after adding first table
  8. Now you have loaded your first table. In the Fields panel it will look something like the image on the right. Rename the Table “Exercise”.
  9. Do the same procedure with the other 4 tables and rename them accordingly.

Step 3: Clean the data

 

Now we have 5 tables loaded, but there are a number of issues: The column names aren’t what we want them to be, there are columns with no data and the columns have default data type string.

  1. Click “Edit queries” in the top menu to open the Query Editor: 
  2. Edit-queries.jpg
  3. Choose the “Exercise”-table
  4. Choose “Use First Row As Headers” in the top menu. As you might notice, in the “Applied steps”-panel, a new row “Promoted Headers” will appear. If you make a mistake you can always remove steps here
  5. Remove the first column which is empty and the second column with rownumbers, and remove any null-columns to the right
  6. Correct decimals. If you have any columns with decimal numbers, you might have to do a simple replace-action. (Google uses ‘.’ (dot) while Power BI uses ‘,’ (coma). This might be different in other language editions. Anyway, we have to change any decimals with dots to coma. Choose the column/columns that have decimal numbers. Choose the “Transform”-tab at the top and click “Replace Values”. Now replace ‘.’ with ‘,’. Notice that the Datekey-column should also be a whole number.
  7. In our case the rest of the columns should have type “Whole number”
  8. We do the same procedure for our other tables. (In the Time-table the Datekey column should be a Whole number, while the Date column which should have a real date-format should have format Date. Other fields such as Year, Month and Day should normally be text fields, while offset-fields should be Whole numbers)

Now all the data is clean and in a nice format. Power BI will understand that Datekey is the common key, and will automatically join the tables on this column. The Fields panel will now look something like the screenshot to the right. Now we can create some visualizations and reports!

Fields-pane-final.jpg

Now go to the final part: Part 4: Some Power BI visualizations and correlations

 

 

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.

It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

      -- Data model to support XML exchange with thecontent repository of
      -- the ArsDigita Community System

      -- Copyright (C) 1999-2000 ArsDigita Corporation
      -- Author: Karl Goldstein (karlg@arsdigita.com)

      -- $Id: content-xml.sql,v 1.3 2001/04/28 19:58:39 donb Exp $

      -- This is free software distributed under the terms of the GNU Public
      -- License.  Full text of the license is available from the GNU Project:
      -- http://www.fsf.org/copyleft/gpl.html

      -- A sequence for uniquely identifying uploaded XML documents until
      -- they are inserted into the repository

      create sequence cr_xml_doc_seq;

      -- create global temporary table cr_xml_docs (
      --     doc_id        integer primary key,
      --     doc           CLOB
      -- ) on commit delete rows;

      create table cr_xml_docs (
          doc_id        integer primary key,
          doc           text
      );

      comment on table cr_xml_docs is '
        A temporary table for holding uploaded XML documents for the
        duration of a transaction, until they can be inserted into
        the content repository.
      ';