how to create a query that paginates?

Hi gotmike,

there is no concept of loops in M (Power Query), but you can use recursive functions, to reach your goals. The following article by Chris Webb uses this concept to flat a parent/child hierarchy. I am not sure how to use this in combination with your API call, but maybe this is one more step into the right direction for you :)

Regards,

Lars

I spent a while on this recently and finally cracked it. Here's some code that will retrieve all companies from Hubspot - some entries may be duplicated (I never figured that out) but a simple next query step of 'remove duplicates' does the trick. It can be used as the basis for all Hubspot API calls and probably similar pagination APIs:

let
Pagination = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Value
   		each  [IsMore] <> false,//  Whilst this is true, keep going
   		each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/companies/v2/companies/paged?hapikey=" & #"Hubspot API Key" & "&properties=name&properties=website&limit=250&offset=" & Text.From([Last_Key]) & "")), // retrieve results per call                 
                 Last_Key = try [WebCall][offset] otherwise 0,
                 IsMore = if [Counter] < 1 then null else [WebCall][#"has-more"],
                 Counter = [Counter]+1,
                 Table = Table.FromRecords(WebCall[companies])
                      ] 
   		,each [Table] // selector
) ,1)

// in
//     Pagination

    ,
    Custom1 = Table.Combine(Pagination)
in
    Custom1

Hello!

After reading most of the Thread and a couple of hours of try and error later, frustration and mind block starts to kick in for me.

I am trying to adapt the code samples to request data from Airtable.com, which uses the same Pagination with an offset-Parameter like in the last example. Maybe someone can help me?? :smileyfrustrated:

To make the reproduction easier, I created a sample database with five entries.

Example how the API works, I am requesting only two records (with the pageSize=2 Parameter) to allow testing with minimal data...

// 20170424221134
// https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2

{
  "records": [
    {
      "id": "rec9xyewCK97T01kP",
      "fields": {
        "Room": "Garage",
        "Condition": "Poor",
        "Priority": "Low"
      },
      "createdTime": "2017-04-24T19:53:01.511Z"
    },
    {
      "id": "recZmk4a5kmxdfK7O",
      "fields": {
        "Condition": "Poor",
        "Priority": "Medium",
        "Room": "Kitchen"
      },
      "createdTime": "2015-11-16T22:48:35.000Z"
    }
  ],
  "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O"
}

The offset-Parameter can be inserted in the next call, to get the next two records:

https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O

To request the whole dataset (without pagination), I would perform the following in Power BI

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
    #"Expanded Column1.fields"

Now I tried to bring together this coding and the example coding from earlier:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1)
, #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
, #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"})
, #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
  #"Expanded Column1.fields"

But I get an error the column "Column1" of the table could not be found. It seems that my "Pagination" is already empty. 

Edit: It seems that the errors comes up because the offset parameter cannot be empty on the first call and can only be filled with valid values, otherwise the API call will fail. I removed the and [Last_Key] <> "" from the third row and now I finally get data (hooray!!) but two records are fetched twice, so instead of five entries it returns seven. Is there any way I can initialize the "Last_Key" and only pass it to the offset-Parameter when it is not initial?

Can anyone help me? Thank you very much!! :smileyhappy:

Best regards

Moritz

Hello!

After reading most of the Thread and a couple of hours of try and error later, frustration and mind block starts to kick in for me.

I am trying to adapt the code samples to request data from Airtable.com, which uses the same Pagination with an offset-Parameter like in the last example. Maybe someone can help me?? :smileyfrustrated:

To make the reproduction easier, I created a sample database with five entries.

Example how the API works, I am requesting only two records (with the pageSize=2 Parameter) to allow testing with minimal data...

// 20170424221134
// https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2

{
  "records": [
    {
      "id": "rec9xyewCK97T01kP",
      "fields": {
        "Room": "Garage",
        "Condition": "Poor",
        "Priority": "Low"
      },
      "createdTime": "2017-04-24T19:53:01.511Z"
    },
    {
      "id": "recZmk4a5kmxdfK7O",
      "fields": {
        "Condition": "Poor",
        "Priority": "Medium",
        "Room": "Kitchen"
      },
      "createdTime": "2015-11-16T22:48:35.000Z"
    }
  ],
  "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O"
}

The offset-Parameter can be inserted in the next call, to get the next two records:

https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O

To request the whole dataset (without pagination), I would perform the following in Power BI

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
    #"Expanded Column1.fields"

The thing is, the offset parameter must be valid and cannot be left empty. For the first API-Call, it must not be sent at all.

I tried to bring together this coding and the example coding from earlier:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),
    #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"records", "offset"}, {"Column1.records", "Column1.offset"}),
    #"Erweiterte Column1.records" = Table.ExpandListColumn(#"Erweiterte Column1", "Column1.records"),
    #"Erweiterte Column1.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.records", "Column1.records", {"id", "fields", "createdTime"}, {"Column1.records.id", "Column1.records.fields", "Column1.records.createdTime"})
in
    #"Erweiterte Column1.records1"

The thing is now, that the first API call seems to be executed twice, at least I get the first two values doubled in in the result set.

Is there any way to "initialize" the Last_Key and only pass the offset-Parameter if the Last_Key is not initialized?

I tried this:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "&init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else "&offset="[WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2"&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),

Which would send an empty "init"-parameter on the first call (the API doesn't mind that), and concatenate the offset-Parameter later. But this only returns the first two entries twice.

Can anyone help me? Thank you very much!! :smileyhappy:

Best regards

Moritz

Hi

I've been watching this thread, in particular the conversation between Imke & kroll, in the hope that I may get the help I need. It seems that solution provided by Imke to kroll might do the trick for me, I want to return all records. I've attempted to reverse engineer the code but it returns an error. I've provided the code with the credentials to a test data source in the hope that someone might be able to assist.

let
Pagination = List.Skip(List.Generate( () => [Last_Key = 1, Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?page='"&[Last_Key]&"'",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
     			Last_Key = if [Counter]<=1 then 1 else WebCall[lastKey] ,// determine the LastKey for the next execution
     			Counter = [Counter]+1,// internal counter
     			#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
     			Value = #"Converted to Table"{1}[Value] // last step of your further queries
                      ], 
   		each [Value]),1),
    Pagination1 = Pagination{0}
in
    Pagination1

Kind Regards - Grant

Hi

Apologies if this is construed as a cross post. if I have a working example of a query that paginates that Imke helped me with - see below.

let
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Page = 1, Counter=0],
   		each  Table.RowCount([Table])>0 or [Counter]=0,
   		each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/opportunities?perPage=100&embed=tags&page="&Text.From([Page])&"",[Headers=[Authorization="Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
     			Page = [Page]+1,
     			Counter = [Counter]+1,
     			Table = Table.FromRecords(WebCall[opportunities])
                      ] 
   		,each [Table]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

So what I'm trying to acomplish now, is create a query that paginates based on the query above but Posts a filter in the body of the request. To put it bluntly, it's doing my head in, so I'm hoping that someone can assist? It uses a different URL however, https://api.capsulecrm.com/api/v2/opportunities/filters/results and I need to post the following in the body of the request;

{  "filter" : {    "conditions": [      {        "field": "isClosed",        "operator": "is",        "value": false      }    ]  }}

This is what I have coded thus far.

let
obj = "{""filter"":{""conditions"":[{""field"":""isClosed"",""operator"":""is"",""value"":false }]}}",
authKey = "Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
url = "https://api.capsulecrm.com/api/v2/opportunities/filters/results?perPage=100&page=",
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Page = 1, Counter=0],
           each  Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
           each [ WebCall = Json.Document(Web.Contents(url&Text.From([Page]) & obj,[Headers=[#"Authorization"=authKey, #"Content-Type"="application/json"],Content = Text.ToBinary(obj)])),
                 Page = [Page]+1,
                 Counter = [Counter]+1,// internal counter
                 Table = Table.FromRecords(WebCall[opportunities])
                      ] 
           ,each [Table]
) ,1)
in
    Pagination

Obviously the code above does not work, otherwise I wouldnt be posting here :smileywink:. I do however think I'm on the right track. See error produced below;

DataSource.Error: Web.Contents failed to get contents from 'https://api.capsulecrm.com/api/v2/opportunities/filters/results?perPage=100&page=1%7B%22filter%22:%7B%22conditions%22:%5B%7B%22field%22:%22isClosed%22,%22operator%22:%22is%22,%22value%22:false%20%7D%5D%7D%7D' (400): Bad Request
Details:
    DataSourceKind=Web
    DataSourcePath=https://api.capsulecrm.com/api/v2/opportunities/filters/results
    Url=https://api.capsulecrm.com/api/v2/opportunities/filters/results?perPage=100&amp;page=1%7B%22filter%22:%7B%22conditions%22:%5B%7B%22field%22:%22isClosed%22,%22operator%22:%22is%22,%22value%22:false%20%7D%5D%7D%7D

Hope someone can help

Kind Regards - Grant

Hi ImkeF,

How would I go about doing this with a JIRA call, where I have a startAt and total number of records? I've included them below as an example, I can't get my head around how I would call them? Any help pointing me in the right direction would be great thanks.

let
Pagination = startAt": 0 "total": 161
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each  [WebCall = Json.Document(Web.Contents("https://companyName.atlassian.net/rest/agile/1.0/board?startAt"&??,[Headers=[ContentType="application/json", Authorization="Auth="]])), // retrieve results per call
     			Last_Key = if [Counter]<=1 then 1 else WebCall[lastKey] ,// determine the LastKey for the next execution
     			Counter = [Counter]+1,// internal counter
     			#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
     			Value = #"Converted to Table"{1}[Value] // last step of your further queries
                      ], 
   		each [Value]),1),
    Pagination1 = Pagination{0}
in
    Pagination1

Hello, I need help to load in Power BI more than 100 records from Airtable.
I do not understand this programming language, so even though I have reviewed all the examples I do not understand what I should do.
Here is my code, I did it using the power BI interface to import data and convert into a table.
I need help!!! Thank you

gotmike or ImkeF I am using the HubSpot Contacts API to get all contacts from the CRM. I am running into the same issue with paging through the contact list. I have been reading through the comments and trying different approaches, but unfortunately without any luck. The parameter for &vidOffset can be used in the url to page through contacts, but needs to be sent in the second call and is based on the the vid-offset value in the first call. Any help on this would be greatly appreciated. 

Okay, so I'm the OP on this and unfortunately I lost track of this post for a couple YEARS... whoops.

But now I got back to it and in looking through all the responses, it seems to be a very hot topic.

My original question was about HubSpot CRM specifically and pagination in general.

I have finally found a solution, which I will post here for everyone else's benefit.

But first, a couple notes:

1. My goal here was to get DEAL info from HubSpot CRM. Presumably, the same process would work for any other info from HubSpot like CONTACTS or COMPANIES. Also would probably work for many others who have a similar use case.
2. Like many APIs out there, they PAGINATE the results, by default they will give you 100 per API call, but you can request up to 250 per call.
3. Like many APIs that paginate, they also give you an OFFSET, so you provide that offset on subsequent calls to get additional pages.
4. Like many APIs that paginate, they will give you a boolean indicator if there are more or not.
5. Like many APIs out there, the HubSpot API breaks data up into separate API calls, so the first one gets you a list of IDs, which you then must use to make a subsequent call for each individual item.
6. Like many APIs out there, the times sent are in unix timestamps. My query converts these to regular time, albeit hard-coded to Eastern Daylight Time.
7. I believe all the fields I'm using in this are standard HubSpot fields, but there is a possibility there are some custom ones. If so, then you may get an error and have to remove those.
8. I use two HubSpot API endpoints here, the first one that gives me a list of ALL DEALS with their dealId is https://api.hubapi.com/deals/v1/deal/paged. The second one that gives me individual deal properties is https://api.hubapi.com/deals/v1/deal/[dealId].
9. Be aware that depending on your number of DEALS, this can take a really long time to execute. It is definitely not "efficient" and is not optimized in any way. I have over 4,000 deals and it takes maybe a half-hour or so.
10. Also, be aware that HubSpot has a daily limit on API calls of about 40,000. So running this query with 4,000 deals will eat up a lot of your API calls.
11. I believe I can solve #7 and #8 above by using some combination of two things to optimize this query:
    1. Specifying specific PROPERTIES in the initial API call may prevent me from having to make a separate call for each individual deal, if I know the exact properties I want
    2. Instead of using the above API endpoint for ALL DEALS, I can use the one for RECENTLY CREATED DEALS and specify a start date. This endpoint is https://api.hubapi.com/deals/v1/recent/created.

At any rate, with all the above caveats, here is the query which works for me and includes all the expanded tables/fields to get to the individual values.

None of the other solutions worked for me, but I used several of them to get to this point. Thanks to everyone for your help.

If I am able to speed this up using the items in #10 above, I will post that solution as well.

let
    // Start Values
    Pagination = List.Skip(List.Generate( () => [hsOffset = 0, Counter = 0, isMore = true],

   	   // Condition under which the next execution will happen
           each  [isMore] <> false,

           // retrieve results per call
           each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/paged?hapikey=[ENTER-YOUR-API-KEY-HERE]&limit=250&offset=" & Text.From(hsOffset))),
           
           hsOffset = try [WebCall][offset] otherwise 0,

           isMore = if [Counter] <1 then null else [WebCall][#"hasMore"],

           // internal counter
    	   Counter = [Counter] + 1

//           ,Table = Table.FromRecords(WebCall[deals])

            ]

//            ,each [Table]

    ),1),
    #"Converted to Table1" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"WebCall", "hsOffset", "isMore", "Counter"}, {"Column1.WebCall", "Column1.hsOffset", "Column1.isMore", "Column1.Counter"}),
    #"Expanded Column1.WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.WebCall", {"deals", "hasMore", "offset"}, {"Column1.WebCall.deals", "Column1.WebCall.hasMore", "Column1.WebCall.offset"}),
    #"Expanded Column1.WebCall.deals" = Table.ExpandListColumn(#"Expanded Column1.WebCall", "Column1.WebCall.deals"),
    #"Expanded Column1.WebCall.deals1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals", "Column1.WebCall.deals", {"dealId", "isDeleted"}, {"Column1.WebCall.deals.dealId", "Column1.WebCall.deals.isDeleted"}),

    // THIS IS THE SECOND API CALL FOR DEAL DETAILS
    #"Added Custom" = Table.AddColumn(#"Expanded Column1.WebCall.deals1", "DealInfo", each Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/" & Text.From([Column1.WebCall.deals.dealId]) & "?hapikey=[ENTER-YOUR-API-KEY-HERE]"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1.WebCall.offset", Int64.Type}, {"Column1.WebCall.hasMore", type logical}}),
    #"Expanded DealInfo" = Table.ExpandRecordColumn(#"Changed Type", "DealInfo", {"properties"}, {"DealInfo.properties"}),
    #"Expanded DealInfo.properties" = Table.ExpandRecordColumn(#"Expanded DealInfo", "DealInfo.properties", {"dealname", "createdate", "hubspot_owner_id", "hs_analytics_source", "deal_temperature", "hs_createdate", "dealtype", "closed_lost_reason", "closedate", "pipeline", "notes_last_contacted", "dealstage", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "amount"}, {"DealInfo.properties.dealname", "DealInfo.properties.createdate", "DealInfo.properties.hubspot_owner_id", "DealInfo.properties.hs_analytics_source", "DealInfo.properties.deal_temperature", "DealInfo.properties.hs_createdate", "DealInfo.properties.dealtype", "DealInfo.properties.closed_lost_reason", "DealInfo.properties.closedate", "DealInfo.properties.pipeline", "DealInfo.properties.notes_last_contacted", "DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount"}),
    #"Expanded DealInfo.properties.dealstage" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties", "DealInfo.properties.dealstage", {"value"}, {"DealInfo.properties.dealstage.value"}),
    #"Expanded DealInfo.properties.hs_analytics_source_data_2" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", {"value"}, {"DealInfo.properties.hs_analytics_source_data_2.value"}),
    #"Expanded DealInfo.properties.hs_analytics_source_data_1" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", {"value"}, {"DealInfo.properties.hs_analytics_source_data_1.value"}),
    #"Expanded DealInfo.properties.amount" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount", {"value"}, {"DealInfo.properties.amount.value"}),
    #"Expanded DealInfo.properties.notes_last_contacted" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.amount", "DealInfo.properties.notes_last_contacted", {"value"}, {"DealInfo.properties.notes_last_contacted.value"}),
    #"Expanded DealInfo.properties.pipeline" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.notes_last_contacted", "DealInfo.properties.pipeline", {"value"}, {"DealInfo.properties.pipeline.value"}),
    #"Expanded DealInfo.properties.closedate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.pipeline", "DealInfo.properties.closedate", {"value"}, {"DealInfo.properties.closedate.value"}),
    #"Expanded DealInfo.properties.closed_lost_reason" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closedate", "DealInfo.properties.closed_lost_reason", {"value"}, {"DealInfo.properties.closed_lost_reason.value"}),
    #"Expanded DealInfo.properties.dealtype" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closed_lost_reason", "DealInfo.properties.dealtype", {"value"}, {"DealInfo.properties.dealtype.value"}),
    #"Expanded DealInfo.properties.hs_createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealtype", "DealInfo.properties.hs_createdate", {"value"}, {"DealInfo.properties.hs_createdate.value"}),
    #"Expanded DealInfo.properties.deal_temperature" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_createdate", "DealInfo.properties.deal_temperature", {"value"}, {"DealInfo.properties.deal_temperature.value"}),
    #"Expanded DealInfo.properties.hs_analytics_source" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.deal_temperature", "DealInfo.properties.hs_analytics_source", {"value"}, {"DealInfo.properties.hs_analytics_source.value"}),
    #"Expanded DealInfo.properties.hubspot_owner_id" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source", "DealInfo.properties.hubspot_owner_id", {"value"}, {"DealInfo.properties.hubspot_owner_id.value"}),
    #"Expanded DealInfo.properties.createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hubspot_owner_id", "DealInfo.properties.createdate", {"value"}, {"DealInfo.properties.createdate.value"}),
    #"Expanded DealInfo.properties.dealname" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.createdate", "DealInfo.properties.dealname", {"value"}, {"DealInfo.properties.dealname.value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DealInfo.properties.dealname",{{"DealInfo.properties.createdate.value", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"DealInfo.properties.closedate.value", Int64.Type}, {"DealInfo.properties.notes_last_contacted.value", Int64.Type}, {"DealInfo.properties.hs_createdate.value", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Create.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, -4, 0, [DealInfo.properties.createdate.value]/1000)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Create.DateTime", type datetime}})
in
    #"Changed Type3"