GoogleAnalytics.Accounts

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function GoogleAnalytics.Accounts

The M code behind the GoogleAnalytics.Accounts M function is responsible for establishing a connection to the Google Analytics API, authenticating the user, and retrieving the data requested by the user. In this article, we will dive deep into the M code that powers this function and understand how it works.

Setting up the Connection

The first step in retrieving data from the Google Analytics account is to establish a connection to the Google Analytics API. This is done by using the following M code:

let

Source = GoogleAnalytics.Accounts(),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Id”, type text}})

in

#”Changed Type”

The GoogleAnalytics.Accounts() function returns a table containing the list of Google Analytics accounts available to the user. The function does not require any parameters and can be called with an empty argument list. Once the list is retrieved, it is transformed to change the data type of the "Id" column to text.

Authenticating the User

Once the list of accounts is retrieved, the user needs to authenticate themselves with the Google Analytics API. This is done by using the following M code:

let

Source = GoogleAnalytics.Accounts(

[

#”Client ID”=”xxxxxx”,

#”Client Secret”=”xxxxxx”,

#”Refresh Token”=”xxxxxx”

]

),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Id”, type text}})

in

#”Changed Type”

The GoogleAnalytics.Accounts() function is called with a record containing the client ID, client secret, and refresh token. These values are obtained by creating a new project in the Google Developers Console, enabling the Google Analytics API, and creating OAuth credentials. Once the credentials are created, the client ID, client secret, and refresh token are obtained and can be used to authenticate the user.

Retrieving Data

Once the user is authenticated, data can be retrieved from the Google Analytics account. This is done by using the following M code:

let

Source = GoogleAnalytics.Accounts(

[

#”Client ID”=”xxxxxx”,

#”Client Secret”=”xxxxxx”,

#”Refresh Token”=”xxxxxx”

]

),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Id”, type text}}),

#”Selected Columns” = Table.SelectColumns(#”Changed Type”,{“Id”}),

#”Added Custom” = Table.AddColumn(#”Selected Columns”, “Custom”, each GoogleAnalytics.Account(#”Changed Type”[Id]{0}, #”FromDate”, #”ToDate”, #”Metrics”, #”Dimensions”, #”Filters”, #”Segments”, #”Sort”, #”MaxResults”])),

#”Expanded Custom” = Table.ExpandTableColumn(#”Added Custom”, “Custom”, {“ga:sessions”, “ga:users”, “ga:pageviews”}, {“ga:sessions”, “ga:users”, “ga:pageviews”})

in

#”Expanded Custom”

The GoogleAnalytics.Account() function is called with parameters that specify the from date, to date, metrics, dimensions, filters, segments, sort, and max results. Once the data is retrieved, it is transformed to select only the "Id" column, add a custom column that calls the GoogleAnalytics.Account() function, and expand the custom column to retrieve the required data.

The GoogleAnalytics.Accounts M function in Power Query is a powerful tool that enables users to retrieve data from their Google Analytics account. The M code behind this function is responsible for establishing a connection to the Google Analytics API, authenticating the user, and retrieving the data requested by the user. By understanding how this function works, users can leverage the power of Power Query to transform and manipulate data to meet their business needs.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)