***Please note that Integromat evolved into Make in February 2022. All content for Integromat is also valid for Make.***

I’ve always wondered whether you can use Integromat for making your life easier in the BI/analytics world where I am partially coming from. As I’ve been becoming more familiar with Integromat ecosystem I can say “yes“. I’ve been always struggling with extracting data from all the systems I work with into BigQuery which I use as data warehouse for all the projects.

In this article, we’ll look at the following assignment:

1. Retrieve row data from a Google Sheets spreadsheet.
2. Upload it into an existing table in BigQuery as new rows.
3. Refresh a Power BI dataset which is sourced from the BigQuery table.

As always, we need to be as efficient as possible. In the Integromat world, you always aim to consume as few operations as possible since that’s what you are mainly billed for. The second billing unit is “data transfer” but that’s rarely a problem. It’s much “easier” to consume all your operations than to consume the data transfer allowance.

I can already tell you that only 4 operations will be needed to solve the problem regardless how many rows you have in your Google Sheet.

Solution

The following video shows:

1. Inputs – the Google Sheets
2. Module configurations
3. Results

It takes only 1 minute 35 seconds to play the whole thing so I strongly recommend you to watch as it is much easier to understand the setup.

Critical Steps

Let me comment on the logic of the scenario:

1. First, we retrieve the rows from the Google Sheets spreadsheet, then we use the Text Aggregator module. This approach will ensure only 2 operations are consumed to retrieve all your rows from the source Google Sheets spreadsheet.
2. Then we run an INSERT statement via the BigQuery module. We use the aggregated row data from the previous step within the INSERT statement. Pay attention to the syntax to get this right.
3. Finally, we just refresh the dataset in Power BI which is linked to the source table in BigQuery. Your dataset in Power BI needs to already exist before you build a scenario like this one.

You might be questioning why to bother with the dataset update in Power BI when you can set up a refresh schedule directly in the Power BI UI. The thing is that there is always a disconnect between the update time of the underlying data and update time of the dataset. With Integromat, you can just refresh your Power BI dataset as soon as your underlying data is backfilled into BigQuery :).

Conclusion

Even though this was not a real project, I find this example extremely interesting – you can basically upload any data from more than 1000 apps which are currently supported by Integromat into a data warehouse such as BigQuery or Snowflake and you don’t need any complicated (and expensive) business intelligence tools.

Do you want to replicate this for your business yourself? 
GO TO MAKE

Or looking for someone to build your integrations?
HIRE ME