Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Edited Question: Asking about why automatic refresh is failing also...
Data Details:
Specifically my question:
My current query:
let
BaseUrl = "https://lasereye.freshsales.io/api/deals/view/300112/",
Token = "XXXXX",
EntitiesPerPage = 25,
Include = "owner",
GetJson = (QParam) =>
let Options = [Headers=[ #"Authorization" = "Token token=" & Token ], Query = QParam],
RawData = Web.Contents(BaseUrl, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let QParam = [include=Include],
Json = GetJson(QParam),
Count = Json[meta][total_pages]
in Count,
GetPage = (Index) =>
let Page = "page=" & Text.From(Index),
QParam = [page=Page,include=Include],
Json = GetJson(QParam),
Value = Json[deals]
in Value,
EntityCount = List.Max({ EntitiesPerPage, (GetEntityCount() * 25 ) }),
PageCount = GetEntityCount(),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(Table, "Column1", {"id", "name", "amount", "base_currency_amount", "expected_close", "closed_date", "updated_at", "created_at"}, {"Column1.id", ....}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"Column1.created_at", type datetimezone}, {"Column1.closed_date", type date}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Column1.created_at]), type date),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "created_at1"}, {"Column1.id", "ID"}, {"Column1.name", "Title"}, {"Column1.amount", "Amount"}...}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column1.forecast_category", "Column1.avatar", "Column1.deal_freddy_metrics", "Column1.tags"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column1.is_deleted] = false)
in
#"Filtered Rows"
(Removed some of the expand/rename to keep it read-able.
Details on the web API:
https://developer.freshsales.io/api/#list_all_deals
This API is too rigid for Power BI. Pull the data with a different tool. As you suggested use the view Updated_at/descending for the cutoff.
Suggestion on tool / where to store data?
I was thinking simple python script to pull into an excel sheet given the small amount of rows?
A CSV file on a OneDrive, or a Sharepoint List. Excel is not a good data storage vehicle.
Check out the July 2025 Power BI update to learn about new features.