Formula.Firewall

[marcobi]

Dear Community

I got a problem with the Formula.Firewall. I undertake a first request where i gather all deals from pipedrive (pipedrive_deals). Then in the second request I want to use the column deal_id from the pipedrive deals to then make the second request to take the product information of each deal id (each row from other table)

Request 1: Request all dealsRequest 2: Request product for each deal_id

However I get a Formula.Firewall error: Query 'Product_request_basic' (step 'Extended Column1') refers to other queries or steps and therefore cannot access a data source directly. Re-create this data combination.

However, if I only insert the deal_id directly i get acces to it. But I want to have it dynamically and depending on the first request.

Can somebody please help me? I tried a lot and did not find any solution... also pretty new in Power BI.

Thank you in advance! 🙂

Marco

[lbendlin]

Keep everything in one partition

formula firewall - Chris Webb's BI Blog (crossjoin.co.uk)

[marcobi]

//This first part will get all the total deal records

let 
Source = Json.Document(Web.Contents("https://hello.world.com/v1/deals?api_token=1234",[Query=[api_token="1234", limit="1", start="0", get_summary="1"]])), 
#"Converted to Table Record" = Record.ToTable(Source),
Value = #"Converted to Table Record"{2}[Value],
summary = Value[summary],
total_records = summary[total_count],

//This second part, tries to resolve the maximum limit value of 500 that pipedrive have
//Starts 0, 500, 1000, 1500 until the total records

Starts = List.Generate(()=>0, each _ < total_records, each _ + 500), 
#"Converted to Table" = Table.FromList(Starts, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), 
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://hello.world.com/v1/deals?api_token=1234",[Query=[api_token="1234", limit="500", start=[Column1]]]))),

//then is just branding and expanding
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"data"}, {"Custom.data"}),
#"Expanded Custom.data" = Table.ExpandListColumn(#"Expanded Custom", "Custom.data"),
#"Expanded Custom.data1" = Table.ExpandRecordColumn(#"Expanded Custom.data", "Custom.data", {"stage_id", "title", "status", "won_time", "owner_name", "value", "products_count", "last_activity_id", "org_id", "id"}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Expanded Custom.data1",{"id", "Column1", "stage_id", "title", "status", "won_time", "owner_name", "value", "products_count", "last_activity_id", "org_id"}),
    #"Erweiterte org_id" = Table.ExpandRecordColumn(#"Neu angeordnete Spalten", "org_id", {"address"}, {"org_id.address"}),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Erweiterte org_id", each ([status] = "won")),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Gefilterte Zeilen1",{"Column1"}),
    #"Sortierte Zeilen" = Table.Sort(#"Entfernte Spalten",{{"stage_id", Order.Ascending}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Sortierte Zeilen", each true),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"id", type text}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"id", "deal_id"}}),
    #"Gefilterte Zeilen2" = Table.SelectRows(#"Umbenannte Spalten", each true),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Gefilterte Zeilen2",{{"value", Int64.Type}}),
    #"Gefilterte Zeilen3" = Table.SelectRows(#"Geänderter Typ1", each true)
in
    #"Gefilterte Zeilen3"

 So this is the query where i'm taking the deal_id's from

[marcobi]

And this is the query which is referring to the previous query, taking out the deal_ids

//basic code

let
    apiUrl = "https://hello.world.com/v1/deals/",
    apiToken = "1234",
    idsTable = Pipedrive_deals,
    ids = Table.Column(idsTable, "deal_id"),
    #"Entfernte alternative Elemente" = List.Alternate(ids,200,40,0),
    //ids = {"4334", "4237", "4223", "4222"}, // replace with your own list of IDs --> Pipedrive[id]
    getApiResults = (id) =>
        let
            apiEndpoint = apiUrl & id & "/products?api_token=" & apiToken,
            apiResult = Web.Contents(apiEndpoint),
            jsonResult = Json.Document(apiResult),
            data = jsonResult[data]
        in
            data,
    results = List.Transform(#"Entfernte alternative Elemente", each getApiResults(_)),
    filteredResults = List.RemoveNulls(results),
    resultsTable = Table.FromColumns(filteredResults),

    #"Transponierte Tabelle" = Table.Transpose(resultsTable),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"Transponierte Tabelle", "Column1", {"id", "deal_id", "product_id"}, {"Column1.id", "Column1.deal_id", "Column1.product_id"})
in
    #"Erweiterte Column1"

[lbendlin]

Your id must be put into RelativePath and the api_token into the query part of Web.Contents

[marcobi]

Thanks I watched the video. However, after trying a lot i still did not manage to do it. Can you somehow give me a hint in form of code to integrate into one partition?