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.

[ZekO]

Hi,

For fnGetToken in the "body" variable you are using client informations so you should use the relative path for that too.

[Mohan128256]

@ZekO I did tried many ways but unable to get that right relative path for the fngettoken.

Could you please give me the right relative path for it please.

Thanks,

Mohan V.

[Mohan128256]

@lbendlin @v-kongfanf-msft

Please help

[Mohan128256]

@lbendlin @v-kongfanf-msft

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.

[lbendlin]

No need to reinvent the wheel.

GitHub - migueesc123/PowerBIRESTAPI: A Microsoft Power BI Data Connector or Power Query Connector fo...

[Mohan128256]

@lbendlin This approach is working for only orgonizational account but not for anonymus.

Am i doing anything wrong here.

[Mohan128256]

@lbendlin This is a old report which been developed and published.

So, for the time being, i need to make schedule of this dataset in powerbi service.

I have to get it done through this way.

Could you please guide me here.

Thanks,

Mohan V

[lbendlin]

Web.Contents - PowerQuery M | Microsoft Learn Example 1