dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed

[Anonymous]

Hello All,

I have given an existing report which fetches the data using power bi rest api and we built a report out of the data.

below is the power query code we have to get all reports data

let
   TenantGUID = ParamTenantID,
   ApplicationID= #"ParamClientID",
   ApplicationSecret= ParamClientSecret,

   OAuthUrl = Text.Combine({"https://login.microsoftonline.com/",TenantGUID,"/oauth2/token"}),
   ClientId = Text.Combine({"client_id",ApplicationID}, "="),
   ClientSecret = Text.Combine({"client_secret", Uri.EscapeDataString(ApplicationSecret)}, "="),
   GrantType = Text.Combine({"grant_type", "client_credentials"}, "="),
   Body = Text.Combine({"resource=https://analysis.windows.net/powerbi/api", ClientId, ClientSecret, GrantType}, "&"),

   AuthResponse= Json.Document(Web.Contents(
       OAuthUrl,
       [
           Content=Text.ToBinary(Body)
       ]
   )),

   AccessToken= AuthResponse[access_token],
   Bearer = Text.Combine({"Bearer", AccessToken}, " "),
   Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/reports/",
                     [Headers=[Authorization=Bearer]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id", "reportType", "name", "webUrl", "embedUrl", "datasetId", "appId", "createdDateTime", "modifiedDateTime", "description", "modifiedBy", "createdBy", "endorsementDetails", "sensitivityLabel", "users", "subscriptions", "workspaceId"}, {"value.id", "value.reportType", "value.name", "value.webUrl", "value.embedUrl", "value.datasetId", "value.appId", "value.createdDateTime", "value.modifiedDateTime", "value.description", "value.modifiedBy", "value.createdBy", "value.endorsementDetails", "value.sensitivityLabel", "value.users", "value.subscriptions", "value.workspaceId"}),
    #"Expanded value.endorsementDetails" = Table.ExpandRecordColumn(#"Expanded value1", "value.endorsementDetails", {"endorsement"}, {"value.endorsementDetails.endorsement"}),
    #"Expanded value.sensitivityLabel" = Table.ExpandRecordColumn(#"Expanded value.endorsementDetails", "value.sensitivityLabel", {"labelId"}, {"value.sensitivityLabel.labelId"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded value.sensitivityLabel",{{"@odata.context", type text}, {"@odata.count", Int64.Type}, {"value.id", type text}, {"value.reportType", type text}, {"value.name", type text}, {"value.webUrl", type text}, {"value.embedUrl", type text}, {"value.datasetId", type text}, {"value.appId", type text}, {"value.createdDateTime", type datetime}, {"value.modifiedDateTime", type datetime}, {"value.description", type any}, {"value.modifiedBy", type text}, {"value.createdBy", type text}, {"value.endorsementDetails.endorsement", type any}, {"value.sensitivityLabel.labelId", type any}, {"value.users", type any}, {"value.subscriptions", type any}, {"value.workspaceId", type text}}),
    #"Expanded value.users" = Table.ExpandListColumn(#"Changed Type", "value.users"),
    #"Expanded value.subscriptions" = Table.ExpandListColumn(#"Expanded value.users", "value.subscriptions"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded value.subscriptions", "value.modifiedBy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"value.modifiedBy.1", "value.modifiedBy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"value.modifiedBy.1", type text}, {"value.modifiedBy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"value.modifiedBy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"value.modifiedBy.1", "P/J#"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"value.createdDateTime", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"value.id", "Report ID"}, {"value.reportType", "Report Type"}, {"value.name", "Report Name"}, {"value.webUrl", "Report Web URL"}, {"value.embedUrl", "Report Embed URL"}, {"value.datasetId", "Dataset ID"}, {"value.appId", "App ID"}, {"value.createdDateTime", "Report Created Date"}, {"value.modifiedDateTime", "Report Modified Date"}, {"value.description", "Report Description"}, {"value.createdBy", "Report Created By"}, {"value.endorsementDetails.endorsement", "Report Endorsment"}, {"value.sensitivityLabel.labelId", "Sensitivity Label"}, {"value.users", "Report Users"}, {"value.subscriptions", "Report Subscriptions"}, {"value.workspaceId", "Report Workspace ID"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"@odata.context", "Report Web URL", "Report Embed URL", "@odata.count"})
in
    #"Removed Columns1"

After publishing this report to power bi service, i see it is throwing an error as

I did tried to make the url as relativepath, but unfortunately it is not working.

Could you please help me to create and correct the power query to create relativepath.

Thanks

[Anonymous]

@Anonymous Thanks for the reply.

I am new to power query and power bi and i am yet to know lot of things from power query end.

Functions and relative paths is something i am yet to reach and understand it better.

But this is came at top prioty than others and i should complete this by today.

Unfortunately I do not have much of understanding and i am learning it now.

It would be great help if you could spend couple of minutes and help me with correcting the code here.

Thanks,

Mohan V.

[Anonymous]

Hi @Anonymous ,

Refreshing dynamic data sources directly in the Power BI service is not supported.

To refresh dynamic data sources in Power BI Service, you can use Web. Contents() and Relative Path parameter in it, please refer:

Web.Contents - PowerQuery M | Microsoft Learn

Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk)

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code (crossjoin.co.uk)

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!