Shcedule refresh isn't working with parametr datasource

[Anonymous]

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?

[lbendlin]

This may help

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

You will need to mask the initial query.

But that's the least of your problems. How do you acquire the Bearer token in the service?

[lbendlin]

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]

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]
db1	Table
db2	Table
db3	Table
db4	Table

same problem if i get data by api