MB-260: Ingest and transform data using Power Query connectors

Let’s review how to use Microsoft Power Query(MPQ) as your data source. And how you can ingest and transform that data. This post will be an overview of the MPQ as we’ll go further in depth with the section “Transform, cleanse, and load data by using Power Query”.

First, you go to data and add your data source, you then select Microsoft Power Query.

After giving the source a name and clicking next, you get the option to pick your source. As you can tell from the screenshot, you have a lot of options to pick from.

By clicking “View more ->” you can see the different connections you can use out of the box with MPQ. Select the one where your data lives. Be aware that if you select to upload a file and you want to choose it from OneDrive, it has to be the OneDrive that you’re currently logged into, you cannot add another OneDrive account, it will fail.

Maybe you have your data stored somewhere else, this could be in a Google sheet for example. You can upload that and get your data from the sheet.

I can also create a blank table and manually enter the data.

A blank query is where your developers can have fun, they write their own queries. Be aware that the blank query uses the Power Query M formula language.

For this post, I’ve used the Google Analytics connection as a new source and selected two columns in my blog on GA (which doesn’t have any data).

Once I’ve selected my columns, I can now transform the data(or I could have if I’d had any data in GA).

When I click “Next”, I can choose whether to refresh the data manually or automatically. If you want to refresh it automatically, you can set how often the refresh should be.

As I mentioned at the start of the post, we’ll go deeper into how we transform and work with the data in later posts.