Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Syncing Airtable with Microsoft Excel via M power query

Topic Labels: Sync
170 0
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi there,

I’m trying to sync Airtable base/view with an Microsoft Excel via the M query template bellow and it almost return what I want. The only problem is that it don’t include the empty cells in the second record even if the first record in the table is completely filled out. How can I modify the Query to allow for empty cells in a table?

let Pagination = List.Skip(List.Generate( () => [Last_Key = “init”, Counter=0],

each [Last_Key] <> null,

each [

Last_Key = try if [Counter]<1 then “”

else

 [WebCall][Value][offset] otherwise null,

WebCall = try if [Counter]<1

then

Json.Document(Web.Contents(“https://api.airtable.com”,

 [RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?api_key="&API_KEY&""]))

else

Json.Document(Web.Contents(“https://api.airtable.com”,

 [RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?api_key="&API_KEY&"&offset="&Last_Key&""])),

Counter = [Counter]+1

],

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”

0 Replies 0