Excel on Sharepoint Online into Power BI via Microsoft Graph API

[HippoKant]

Hi,

I have a requirement that I need to populate Power BI with data from Excel on Sharepoint online (either Site or Teams), however, I cannot use the Sharepoint connector or any other solution. It must be via Graph API. 

When using the graph api developer - I do get the desired data using the following endpoint:

https://graph.microsoft.com/v1.0/groups/{ID}/drive/root:/general/rapport1.xlsx:/workbook/worksheets('Sheet1')/usedRange

However, when I use the same in PowerBI I get error: DataFormat.Error: We were unable to resolve the type name 'workbookRange' to an EdmType

Googling this error and applying the suggested solutions doesn't help. I suspect the connecter is incorrect.

This is the query:

token_uri = "https://login.windows.net/" & #"Azure AD Tenant ID" & "/oauth2/token",
resource="https://graph.microsoft.com",
tokenResponse = Json.Document(Web.Contents(token_uri,
[
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"Application SPO",
resource = resource,
grant_type = "client_credentials",
client_secret = #"Application SPO Secret"
]
)),
Headers = [Accept = "application/json"], ManualStatusHandling = {400}
])),
access_token = tokenResponse[access_token],

Source = OData.Feed("https://graph.microsoft.com/v1.0/groups/XXXX/drive/root:/general/rapport.xlsx:/workbook/worksheets('...", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
in
Source

Any help would be highly appreciated

[LastOfMyKind]

Same here, bumping against the wall.

I think the cause is in the format of the api query response, it's not purely row/column-based, like, for example, managedDevices, where pbi can easily transform it from json to M query table. It needs some transposition or something on the M query level so that it fits pbi table design but my knowledge ended here 😞