SG data into Power Bi

Flow Production Tracking
1

Hi;

Anybody here was able to pull data from SG to Power Bi using rest API.

I’ve done it and I’m able to extract data of different entities. It works like a charm when I’m using the method “read one record”, but when I jump to use the method “Read All records”, it’s working somehow, and it extract some levels of the dictionary, but when I drill down and reach the attributes level, it shows a blank.

I was wondering if someone has tried to do something similar and if there’s a way to handle this.

Thanks

1 Like
2

Hi;
I could solve it.

You can actually do a direct query using SG API rest.
First of all, don’t forget to create the client credentials for it at SG using the script entity.
Then go to PowerBI. I suggest you to start with a blank query.
Then you go to advance editor. The language that you must use is “M” and you can write something like this.

let
token_url = “https://[URL]/api/v1.1/auth/access_token”,
assets_url = “https://[URL]/api/v1.1/entity/sequences?fields=*&filter[project.Project.code]=[code]”,

GetJson = Web.Contents(token_url,
[
Headers = [#“Accept”=“application/json”,
#“Content-Type”=“application/x-www-form-urlencoded”],
Content = Text.ToBinary(“client_id=user&client_secret=password&grant_type=client_credentials”)
]
),

FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,

GetJson1 = Json.Document(Web.Contents(assets_url , [Headers=[Authorization= AccessTokenHeader ]])),
data = GetJson1[data],

// Convert the list of records to a table
dataTable = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Expand the record column to individual columns
expandedTable = Table.ExpandRecordColumn(dataTable, "Column1", Record.FieldNames(dataTable{0}[Column1]))

in
expandedTable

At the beginning what you do is building the headers for the query to make sure that you authenticate properly. Then you get the Json with the information you are pulling, but you’ll have to struggle a bit to shape the information into a useful table for powerBI.

I’m afraid that you’ll have to do a query for each entity in SG you want to work with, and if the query is large enough, you’ll have to handle pagination too.

I hope it helps.
Regards.