Can't extract closed date

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

Hi Jason

Could you please share the code you are using for making the API request and the result you get? that would help a lot.

Magho

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

The expected_close_date is only available for pending deals (deals with no won or lost state), close_date is only available for non pending deals (deals with won or lost state).

So for the deals with the won state, you won't find an expected_close_date column.

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

Hi Jason,

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 close_date instead.

If still there is a problem, please try to share a sample of the API response to make the situation clearer.

Magho

jason_w at December 30th, 2021 03:33 — #7

Hi Magho
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

Hi Jason,

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 expected_close_date.

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 expected_close_date and close_date.

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 closed and pending deals.

jason_w at December 30th, 2021 21:48 — #9

Hi Magho
I went with your first suggestion and that worked!
Thanks
Jason