jason_w at December 29th, 2021 00:15 — #1
I'm using a Power Query in Power BI with a source of https://app.onepagecrm.com/api/v3/deals to get all deal data. However, when the records are all loaded, there is no field for the closed date. The nearest equivalent is expected_close_date but when I filter on WON deals, this column has only null values.
magho at December 29th, 2021 02:18 — #2
Could you please share the code you are using for making the API request and the result you get? that would help a lot.
jason_w at December 29th, 2021 03:12 — #3
I'm getting an error that says new users can't upload images. However, I'm using Power BI and then a Web Query to get the data using the URL in my opening post. I'm then expanding the table to after all the rows are loaded. There are numerous columns, but only a couple of date columns. The expected_deal_close date I thought was supposed to have the deal close date for WON deals. This is null for all WON deals.
magho at December 29th, 2021 03:38 — #4
expected_close_date is only available for pending deals (deals with no
close_date is only available for non pending deals (deals with
So for the deals with the
won state, you won't find an
jason_w at December 29th, 2021 20:27 — #5
So then why aren't I getting a close_date field coming through the API?
magho at December 30th, 2021 01:59 — #6
The expected_deal_close date I thought was supposed to have the deal close date for WON deals. This is null for all WON deals.
For this case, you have
won deals and this type of deal doesn't have
expected_close_date it only has
close_date. So if you check the
expected_close_date for those deals you will find
null, you need to check the
If still there is a problem, please try to share a sample of the API response to make the situation clearer.
jason_w at December 30th, 2021 03:33 — #7
As explained, it's hard to share the API response because I get an error from this forum that new users can't post images. Hence, I have to resort to text to explain. I don't think you have understood the situation or I haven't explained well enough. Firstly, I'm calling the API through PowerBI queries. Hence, there is no code, only a URL (as per my posting above) and then the PowerBI functionality to transform the data in a table format. In that table, there are multiple columns representing all the fields called by the API. it calls all deals, both CLOSED, WON and pending. There is no field/column for close_date. These are all the column headers:
amount, total_cost, total_amount, text, status, stage, pipeline_id, owner_id, name.1, name, months, modified_at, margin, id.1, id, has_related_notes, has_deal_items, email, expected_close_date, deal_items, deal_fields, date, created_at, cost, contact_owner_id
magho at December 30th, 2021 06:33 — #8
I think the problem is:
closed deals and
pending deals have different response formats than each other.
closed deals have the
expected_close_date but no
close_date while the
pending deals have
close_date but no
As the response is put into a single table, the PowerBI detects the structure of the table from the first response record which would miss one of the columns
Could you try one of these solutions:
- You can split this query into two queries one for the
closed deals and one for the
pending deals so that you have two tables each having the right format.
- If you can manually change the structure of the table to add a new column for the
close_date and see if it works.
Please try these and let me know if the problem still exists.
We would look into making the response has the same format for both
jason_w at December 30th, 2021 21:48 — #9
I went with your first suggestion and that worked!