Power Query: Relative Path for Power BI Rest API to fetch data sources

[Mohan128256]

Hi Everyone,

I have a fnDatasource function to fetch all datasource by groupid's

(group as text) =>
let
    Source = Json.Document(Web.Contents(https://api.powerbi.com/v1.0/myorg/admin/datasets/ & group &"/datasources", [Headers=[Authorization=fnGetToken()]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"datasourceType", "connectionDetails", "datasourceId", "gatewayId"}, {"value.datasourceType", "value.connectionDetails", "value.datasourceId", "value.gatewayId"}),
    #"Expanded value.connectionDetails" = Table.ExpandRecordColumn(#"Expanded value1", "value.connectionDetails", {"emailAddress", "server"}, {"value.connectionDetails.emailAddress", "value.connectionDetails.server"})
in
    #"Expanded value.connectionDetails"

I need to push this to power bi service and schedule the refresh but unable to because it has dynamic URL.

can any one please help me with relative path for this power query.

Thanks,

Mohan V.

[Anonymous]

Hi @Mohan128256 ,

You can refer to below similiar solution and blog:

Solved: Data source accessing a web URL which requires Aut... - Microsoft Fabric Community

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

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mohan128256]

@Anonymous 

Thanks for your response.

I have tried below changes but still when i publish this, I am getting the warning of dynamic url.

Three parameters been created for 

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

function to get token

fnGetToken

() =>
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}, " ")

in
   Bearer

fnGetReportUsers

(group as text) =>
    let
        Source = Web.Contents(
                        https://api.powerbi.com,
                        [
                            RelativePath = "v1.0/myorg/admin/reports/" & group & "/users",
                            //Query = [includeOptional="customFields,nullCustomFields,postalAddresses"], 
                            Headers=[Authorization=fnGetToken()]
                        ]),
        json_doc = Json.Document(Source) ,
    #"Converted to Table" = Table.FromRecords({json_doc}),
    #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"reportUserAccessRight", "emailAddress", "displayName", "identifier", "graphId", "principalType", "userType"}, {"value.reportUserAccessRight", "value.emailAddress", "value.displayName", "value.identifier", "value.graphId", "value.principalType", "value.userType"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded value1",{{"@odata.context", type text}, {"value.reportUserAccessRight", type text}, {"value.emailAddress", type text}, {"value.displayName", type text}, {"value.identifier", type text}, {"value.graphId", type text}, {"value.principalType", type text}, {"value.userType", type text}})
in
    #"Changed Type"

Reports Table

let
    Source = Json.Document(Web.Contents(https://api.powerbi.com/v1.0/myorg/admin/reports, [Headers=[Authorization=fnGetToken()]])),
    #"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"

ReportsWithUsers Table - Here i am calling the fnGetReportUsers function

let
    Source = Reports,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Report ID"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Other Columns",#"Top Rows"),
    #"Invoked Custom Function" = Table.AddColumn(#"Kept First Rows", "fnGetReportUsers", each fnReportUsers([Report ID])),
    #"Expanded fnGetReportUsers1" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnGetReportUsers", {"@odata.context", "value.reportUserAccessRight", "value.emailAddress", "value.displayName", "value.identifier", "value.graphId", "value.principalType", "value.userType"}, {fnGetReportUsers.@odata.context, "fnGetReportUsers.value.reportUserAccessRight", "fnGetReportUsers.value.emailAddress", "fnGetReportUsers.value.displayName", "fnGetReportUsers.value.identifier", "fnGetReportUsers.value.graphId", "fnGetReportUsers.value.principalType", "fnGetReportUsers.value.userType"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fnGetReportUsers1",{fnGetReportUsers.@odata.context}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"fnGetReportUsers.value.reportUserAccessRight", "User Access Right"}, {"fnGetReportUsers.value.emailAddress", "Email Address"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Email Address", "Email Address - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"fnGetReportUsers.value.displayName", "Display Name"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"fnGetReportUsers.value.identifier"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"fnGetReportUsers.value.graphId", "Graph ID"}, {"fnGetReportUsers.value.principalType", "Principal Type"}, {"fnGetReportUsers.value.userType", "User Type"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns2", "Email Address - Copy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Email Address - Copy.1", "Email Address - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Email Address - Copy.1", type text}, {"Email Address - Copy.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type",{"Email Address - Copy.2"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Email Address - Copy.1", "P/J #"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns3",null,"Distribution List",Replacer.ReplaceValue,{"User Type"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value", {{"P/J #", null}}),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"User Type", null}}),
    #"Replaced Errors2" = Table.ReplaceErrorValues(#"Replaced Errors1", {{"Principal Type", null}}),
   #"Replaced Errors3" = Table.ReplaceErrorValues(#"Replaced Errors2", {{"Graph ID", null}}),
    #"Replaced Errors4" = Table.ReplaceErrorValues(#"Replaced Errors3", {{"Display Name", null}}),
    #"Replaced Errors5" = Table.ReplaceErrorValues(#"Replaced Errors4", {{"Email Address", null}}),
    #"Replaced Errors6" = Table.ReplaceErrorValues(#"Replaced Errors5", {{"User Access Right", null}}),
    #"Replaced Errors7" = Table.ReplaceErrorValues(#"Replaced Errors6", {{"Report ID", null}})
in
    #"Replaced Errors7"

Could you please help me on where I am going wrong in making the relative path in functions.

Thanks in Advance.

Mohan V.

[Greg_Deckler]

 Here's what  had to say when I messaged him on LinkedIn. 

Hey greg. From what I've learned, the base URL cannot contain any dynamic elements. In the above example a piece mentions 'group' SO to be able to make this work, the part of 'Group' als needs to be in the headers section. So most likely this is fine:  https://api.powerbi.com/v1.0/myorg/admin/datasets/

@Mohan128256