Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Shcedule refresh isn't working with parametr datasource

Do not works shceduled refresh with datasource parametr. how to fix it?

Try to get powerbi rest api data.

0. func get api data

getData

 

let //Get or Post query to PowerBI API
    source = (urlApi as text) =>
    let
        uriApi = "https://api.powerbi.com/v1.0/myorg",
        getDataApi = Json.Document(Web.Contents(uriApi,
            [RelativePath = urlApi, Headers = [Authorization = "Bearer "&token], ManualStatusHandling={400}]))[value],
        transformToTable = Table.FromList(getDataApi, Record.FieldValues, Record.FieldNames(getDataApi{0}))
    in
        transformToTable
in
    source

 

1. get list of workspaces

groups

 

let
    // Returns a list of workspaces.
    source = getData("/groups")
in
    source

 

2. try to get list of group users to each groups (step 1)

groups_user

 

let
    // List of workspaces.
    source = Table.FromList(groups[id],null,{"group_id"}),
    // Returns a list of users that have access to the specified workspace.
    addGroupUser = Table.AddColumn(source, "getGroupUsers", each getData("/groups/"&[group_id]&"/users")),
    expandToTable = Table.ExpandTableColumn(addGroupUser, "getGroupUsers",Table.ColumnNames(addGroupUser[getGroupUsers]{0}))
in
    expandToTable

 

in power bi service i get error in sheduled refresh:

[Failed to merge data] Section1/getData/source refers to other queries or stages and therefore cannot directly access the data source. Change this data combination.

 

Ошибка в источнике данных:
{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[Failed to merge data] Section1/getData/source refers to other queries or stages and therefore cannot directly access the data source. Change this data combination."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Web\",\"path\":\"https://api.powerbi.com/v1.0/myorg/groups\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacyError"}}],"exceptionCulprit":1}}} Table: groups_user.
URI кластера:
WABI-WEST-EUROPE-redirect.analysis.windows.net

 

 As far as I understand, the error occurs because the workspace list is a parameter. How to fix it?

Need Help

3 Replies

    • lbendlin's avatar
      lbendlin
      Super User
      6 years ago

      Here is one example of how to mask it

       

      getgroups:

      () => 
  let
    uriApi = "https://api.powerbi.com/v1.0/myorg/",
    getDataApi = Json.Document(
      Web.Contents(
        uriApi, 
        [ RelativePath = "groups"
         , Headers = [Authorization = token]
         , ManualStatusHandling = {400}
        ]
      )
    )[value],
    transformToTable = Table.FromList(
      getDataApi, 
      Record.FieldValues, 
      Record.FieldNames(getDataApi{0})
    )
  in
    transformToTable

      getgroupusers:

      (groupid as text) => 
  let
    uriApi = "https://api.powerbi.com/v1.0/myorg/",
    getDataApi = Json.Document(
      Web.Contents(
        uriApi, 
        [ RelativePath = "groups/" & groupid & "/users"
         , Headers = [Authorization = token]
         , ManualStatusHandling = {400}
        ]
      )
    )[value],
    transformToTable = Table.FromList(
      getDataApi, 
      Record.FieldValues, 
      Record.FieldNames(getDataApi{0})
    )
  in
    transformToTable

       

      query to get group users:

      let
    // Returns a list of workspaces.
    source = getgroups(),
    // only lists premium workspaces
    #"Filtered Rows" = Table.SelectRows(source, each ([isOnDedicatedCapacity] = true)),
    // looks up users for each workspace
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "Users", each getgroupusers([id]))
in
    #"Invoked Custom Function"

       

      This does not show the formula firewall warning.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        thx for answer. problem is not in formula firewall. i can get data in power bi desktop. it work's fine. I have a problem with schedule refresh in power bi service.

         

        problem looks like:

        db_name=Sql.Database(server,db,[Query ="..."])AddColum=Sql.Database(server, [db_name]){[Name = "tableName"]}[Data]
        db1Table
        db2Table
        db3Table
        db4Table

         

        same problem if i get data by api