Forum Discussion
Formula.Firewall
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 colum...
//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
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"Your id must be put into RelativePath and the api_token into the query part of Web.Contents