cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

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.


Did I answer your question? Mark my post as a solution!
Ricardo

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
Community Champion

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

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.


Did I answer your question? Mark my post as a solution!
Ricardo

View solution in original post

Highlighted
Helper I
Helper I

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

Hey @camargos88 ,


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


Thanks!!

Highlighted
Helper I
Helper I

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

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!!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.