Link to Microsoft Power Platform

Flow Production Tracking
1

Has anyone connected Flow to Microsoft Power Platform? I need to build some PowerBI integrations - which I have as a proof of concept using the Python API. But I need to publish the PowerBI reports to the cloud. Unfortunately, the cloud PowerBI doesn’t support Python as a data source like the desktop version. So I need to create a dataflow using an API connecter - has anyone got this to work?

2

Maybe the Rest API works?

3

Hi @MClementson,

We’ve been working with Power BI to analyse our production data for nearly a year now.

The only solution I’ve found is to run a python script that:

  • export all Shotgrid data to json files
  • upload those to Sharepoint
  • load them into Power BI from Sharepoint

That’s not ideal but it’s the easiest solution so far.

Cheers,
Kevin

4

Thanks Kevin - that’s not ideal, but sounds like a solution. I can probably extend upon that and use python Azure functions to push data directly into Dataverse, rather than going via Sharepoint. If I come up with a solution, I’ll let you know.

Thanks.

5

Indeed, it’s not ideal but it was the only solution since, in our use case, we also have other sources of datas than Shotgrid.
We gather all the export files in a central location and then push it to Sharepoint.

Yes, please, let us know if you find a better solution.

6

Hi @MClementson
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.