Combination two Query with API - Error formula.firewall rebuild this data combination

[emerson89]

Hi,

I need to list in my first consultation, the result of the list returned in the second consultation.
- I verified that the formula.firewall error (rebuild this data combination) actually occurs if I do all operations on my second query. So I followed some tips and left only the main part in my second query, bringing the whole part of "breaking" the query to my main query, but even separating it into 2 different queries, I still have the same error when executing my first query.

Therefore, I believe that the ideal would be for me to unite the two consultations totally in a single consultation. I tried to perform this operation, but I do not have experience in lingaugem M, and I was unable to perform this operation.

Query1:

let
    ufnCallAPI = (numPedido) =>

        let
            result = Json.Document(Web.Contents("https://api.vhsys.com/", [RelativePath="v2/pedidos/" & Number.ToText(numPedido)  & "/produtos", 
            Headers=[#"access-token"="KdBYLKTbZRKKMcTJBCLKIUOGSIAfKH", #"secret-access-token"="xxxxxxxxxxxx"]]))  
        in
            result,

        aux = listaPedidos_Samob, /*in this part, I call my second query */
        aux2 = Table.FromList(aux, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        aux3 = Table.ExpandRecordColumn(aux2, "Column1", {"code", "status", "paging", "data"}, {"Column1.code", "Column1.status", "Column1.paging", "Column1.data"}),
        aux4 = Table.ExpandListColumn(aux3, "Column1.data"),
        aux5 = Table.RemoveColumns(aux4,{"Column1.code", "Column1.status", "Column1.paging"}),
        aux6 = Table.ExpandRecordColumn(aux5, "Column1.data", {"id_ped", "id_pedido", "id_cliente", "nome_cliente", "id_local_retirada", "id_local_cobranca", "vendedor_pedido", "vendedor_pedido_id", "listapreco_produtos", "valor_total_produtos", "desconto_pedido", "desconto_pedido_porc", "peso_total_nota", "peso_total_nota_liq", "frete_pedido", "valor_total_nota", "valor_baseICMS", "valor_ICMS", "valor_baseST", "valor_ST", "valor_IPI", "condicao_pagamento_id", "condicao_pagamento", "frete_por_pedido", "transportadora_pedido", "id_transportadora", "data_pedido", "prazo_entrega", "referencia_pedido", "obs_pedido", "obs_interno_pedido", "status_pedido", "contas_pedido", "comissao_pedido", "estoque_pedido", "ordemc_emitido", "data_cad_pedido", "data_mod_pedido", "id_aplicativo", "id_pedido_aplicativo", "lixeira"}, {"id_ped", "id_pedido", "id_cliente", "nome_cliente", "id_local_retirada", "id_local_cobranca", "vendedor_pedido", "vendedor_pedido_id", "listapreco_produtos", "valor_total_produtos", "desconto_pedido", "desconto_pedido_porc", "peso_total_nota", "peso_total_nota_liq", "frete_pedido", "valor_total_nota", "valor_baseICMS", "valor_ICMS", "valor_baseST", "valor_ST", "valor_IPI", "condicao_pagamento_id", "condicao_pagamento", "frete_por_pedido", "transportadora_pedido", "id_transportadora", "data_pedido", "prazo_entrega", "referencia_pedido", "obs_pedido", "obs_interno_pedido", "status_pedido", "contas_pedido", "comissao_pedido", "estoque_pedido", "ordemc_emitido", "data_cad_pedido", "data_mod_pedido", "id_aplicativo", "id_pedido_aplicativo", "lixeira"}),
        pageRange = aux6[id_ped], /*135/5000
here, exactly what I need is listed, with the numbers correctly in my list (if I run the code so far, there are no errors) */

        pages = List.Transform(pageRange, each ufnCallAPI(_))
in
    pages

When executing the above query, the following error is returned:
Formula.Firewall: Query 'Produtos_Samob' (step 'pages') refers to other queries or steps, so you may not have direct access to a data source. Recompile this combination of data.

Query2 (listaPedidos_Samob):

let
    ufnCallAPI = (nPage) =>
        let
            result = Json.Document(Web.Contents("https://api.vhsys.com/", [RelativePath="v2/pedidos?offset=" & Number.ToText(nPage) & "&limit=250", 
            Headers=[#"access-token"="KdBYLKTbZRKKMcTJBCLKIUOGSIAfKH", #"secret-access-token"="xxxxxxxxxxxxxxx"]]))  
        in
            result,

        tmpResult = ufnCallAPI(1),
        auxTotal1 = Record.ToTable(tmpResult),
        Value = auxTotal1{2}[Value],
        auxTotal2 = Value[total],

        totalItems = auxTotal2,
        pageRange = {0..Number.RoundUp(totalItems / 250)-1},
        pages = List.Transform(pageRange, each ufnCallAPI(_*250))
in
    pages

This query above is ok.
I then need to unite the two queries, listing the result of this second query within query 1, to avoid the error "Formula.Firewall".

Thanks for the help

[camargos88 · Accepted solution]

Hi @emerson89 ,

It's a data privacy issue, perhaps on table which you have numPedido (it's passing the parameter to other query).

Check this post: https://blog.crossjoin.co.uk/2018/12/27/data-privacy-settings-data-refresh-performance-power-bi-exce...

Have you tried to set them to the same security level ? Or even public if it's not sensible data.

[emerson89]

Hi @camargos88 , thats fine?

Your tip solved my problem to create my query, however when I update the power BI panel, I get the error AccessForbinddenException to Refresh data. Could you help me, please? I created a new topic for this:

https://community.powerbi.com/t5/Desktop/Error-AccessForbinddenException-to-Refresh-data/m-p/1129703...

Thanks!!

[emerson89]

Hey @camargos88 ,

Thanks for the help, this solution it worked perfectly in my project.

Thanks!!