Airtable Integration - Duplicating Infinite Rows

[dbremer]

Hi,

I've followed the steps at this link to integrate data from Airtable into PowerBI: https://support.airtable.com/docs/visualizing-airtable-records-in-microsoft-power-bi-power-query

Everything appears to go smoothly until the data gets loaded in. The table that I'm specifically trying to load into PowerBI contains 787 rows of data. When it does get loaded into PowerBI, PowerBI appears to be making infinite duplicates of each row. I did let it run for two hours and around 2 billion rows of data were uploaded. What could be causing PowerBI to do this?

[Urba34 · Accepted solution]

Hello guys i have the solution copy past this code 

let
Pagination = List.Skip( List.Generate( () => [Page_Key = "init", Counter=0], // Start Value
each [Page_Key] <> null, // Condition under which the next execution will happen
each [Page_Key = try if [Counter]<1
then ""
else [WebCall][Value][offset]
otherwise null, // determine the LastKey for the next execution
WebCall = try if [Counter]<1
then Json.Document(Web.Contents("https://api.airtable.com/v0/"&BASE_ID&"/"&TABLE_ID,[Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN]]))
else Json.Document(Web.Contents("https://api.airtable.com/v0/"&BASE_ID&"/"&TABLE_ID&"?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN
]])),// retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),
1
),
#"Json2Table" = Table.RenameColumns(Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{"Column1", "stepA.1"}}),
#"stepA.1 développé" = Table.ExpandRecordColumn(Json2Table, "stepA.1", {"Value"}, {"Value"})
in
#"stepA.1 développé"

[dbremer]

Hey Urba34,

I tried this code, but it did not load my data into PowerBI. Did you include everything?

[Urba34]

Yes 

BASE_ID

TABLE_ID

PERSONAL_ACCESS_TOKEN 

it's a parameters in Power BI like that 

[dbremer]

Unfortunately, it's not pulling any data for me.

[dbremer]

Thank you!!!! I was not entering your code above correctly. I had to enter it as:

let
Pagination = List.Skip( List.Generate( () => [Page_Key = "init", Counter=0], // Start Value
each [Page_Key] <> null, // Condition under which the next execution will happen
each [Page_Key = try if [Counter]<1
then ""
else [WebCall][Value][offset]
otherwise null, // determine the LastKey for the next execution
WebCall = try if [Counter]<1
then Json.Document(Web.Contents("https://api.airtable.com/v0/"&BASE_ID&"/"&TABLE_ID,[Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN]]))
else Json.Document(Web.Contents("https://api.airtable.com/v0/"&BASE_ID&"/"&TABLE_ID&"?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer "&PERSONAL_ACCESS_TOKEN
]])),// 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", {"Value"}, {"Column1.Value"}),
#"Expanded Column1.Value" = Table.ExpandRecordColumn(
#"Expanded Column1", "Column1.Value", {"records"}, {"Column1.Value.records"}),
#"Expanded Column1.Value.records" = Table.ExpandListColumn(
#"Expanded Column1.Value", "Column1.Value.records"),
#"Expanded Column1.Value.records1" = Table.ExpandRecordColumn(
#"Expanded Column1.Value.records", "Column1.Value.records",
{"id", "fields", "createdTime"},
{"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}),
#"Renamed Columns" = Table.RenameColumns(
#"Expanded Column1.Value.records1",{{"Column1.Value.records.id", "_airtableRecordId"},
{"Column1.Value.records.createdTime", "_airtableRecordCreatedAt"},
{"Column1.Value.records.fields", "_airtableRecordFields"}}),
#"Reordered Columns" = Table.ReorderColumns(
#"Renamed Columns",
{"_airtableRecordId", "_airtableRecordCreatedAt", "_airtableRecordFields"}),
#"Expanded Record Fields" = Table.ExpandRecordColumn(
#"Reordered Columns", "_airtableRecordFields",
Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]),
Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]))
in
#"Expanded Record Fields"

[Ron1964]

Same here

[Urba34]

Same issue for me