Extract records from Excel file

This worked, Thanks! I was able to extract the filename. How do I extract the content of each file now?

This is my code:

let
// Pagination Logic: This part handles pagination by making API calls to Airtable with different offsets to retrieve paginated data.
Pagination = List.Skip(
List.Generate(
() => [Page_Key = "init", Counter=0], // Initialize page key and counter
each [Page_Key] <> null, // Continue generating while Page_Key is not null
each [
Page_Key = try if [Counter] < 1 then "" else [WebCall][Value][offset] otherwise null, // Determine the next Page_Key
WebCall = try if [Counter] < 1 then
// Initial API call without offset
Json.Document(
Web.Contents(
"https://api.airtable.com",
[
RelativePath = "v0/" & BASE_ID & "/" & TABLE_ID,
Headers = [Authorization = "Bearer " & PERSONAL_ACCESS_TOKEN]
]
)
)
else
// Subsequent API calls with offset
Json.Document(
Web.Contents(
"https://api.airtable.com",
[
RelativePath = "v0/" & BASE_ID & "/" & TABLE_ID & "?offset=" & [WebCall][Value][offset],
Headers = [Authorization = "Bearer " & PERSONAL_ACCESS_TOKEN]
]
)
),
Counter = [Counter] + 1 // Increment the counter for each iteration
],
each [WebCall]
),
1
),

// Convert the paginated data into a table
#"Converted to Table" = Table.FromList(
Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error
),

// Expand and structure the paginated data
#"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"}
),

// Rename columns to align with a specific naming convention.
#"Renamed Columns" = Table.RenameColumns(
#"Expanded Column1.Value.records1",
{
{"Column1.Value.records.id", "_airtableRecordId"},
{"Column1.Value.records.createdTime", "_airtableRecordCreatedAt"},
{"Column1.Value.records.fields", "_airtableRecordFields"}
}
),

// Reorder columns to the desired order.
#"Reordered Columns" = Table.ReorderColumns(
#"Renamed Columns",
{"_airtableRecordId", "_airtableRecordCreatedAt", "_airtableRecordFields"}
),

// Expand the record fields dynamically based on distinct field names, ensuring that all fields are expanded regardless of schema changes.
#"Expanded Record Fields" = Table.ExpandRecordColumn(
#"Reordered Columns", "_airtableRecordFields",
List.Distinct(List.Combine(List.Transform(
List.Transform(Table.ToRecords(#"Reordered Columns"), each Record.Field(_, "_airtableRecordFields")),
each Record.FieldNames(_)
))),
List.Distinct(List.Combine(List.Transform(
List.Transform(Table.ToRecords(#"Reordered Columns"), each Record.Field(_, "_airtableRecordFields")),
each Record.FieldNames(_)
)))
),
#"Sorted Rows" = Table.Sort(#"Expanded Record Fields",{{"Quarter Reporting On", Order.Ascending}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows",{"_airtableRecordId", "_airtableRecordCreatedAt", "Quarter Reporting On", "Please input the number of overall residents supported in this quarter?", "Number of residents aged 65+ mailed", "Number of flyers/posters distributed", "Number of financial gains from the pension credit", "Number of pension credit issues handled", "Please state below the total amount of financial gains that service users have been able to make this quarter?", "Number of CoL appointments (including home visits)", "Number of wellbeing and wraparound home visits", "Please can you provide demographic information on the service users who received CoL appointments, especially home visits? copy", "In what wards have flyers and posters been distributed?", "Please can you provide below the key issues that are facing service users who have had wellbeing and wraparound visits?", "Please can you provide demographic information on the service users who received CoL appointments, especially home visits?", "Quarterly", "Created By", "Please provide the demographic information on the service users who have received wellbeing and wraparound visits?", "Quarterly Case Study Attachment"}),
#"Extracted Values" = Table.TransformColumns(#"Reordered Columns1", {"In what wards have flyers and posters been distributed?", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Values",{{"Please state below the total amount of financial gains that service users have been able to make this quarter?", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "_airtableRecordCreatedAt", Splitter.SplitTextByEachDelimiter({"T"}, QuoteStyle.Csv, false), {"_airtableRecordCreatedAt.1", "_airtableRecordCreatedAt.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"_airtableRecordCreatedAt.1", type date}, {"_airtableRecordCreatedAt.2", type time}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"_airtableRecordCreatedAt.2", "Time"}, {"_airtableRecordCreatedAt.1", "Date"}, {"_airtableRecordId", "ID"}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"ID", "Date", "Time", "Quarter Reporting On", "Please input the number of overall residents supported in this quarter?", "Number of residents aged 65+ mailed", "Number of flyers/posters distributed", "Number of financial gains from the pension credit", "Number of pension credit issues handled", "Please state below the total amount of financial gains that service users have been able to make this quarter?", "Number of CoL appointments (including home visits)", "Number of wellbeing and wraparound home visits", "Please can you provide demographic information on the service users who received CoL appointments, especially home visits? copy", "In what wards have flyers and posters been distributed?", "Please can you provide below the key issues that are facing service users who have had wellbeing and wraparound visits?", "Please can you provide demographic information on the service users who received CoL appointments, especially home visits?", "Please provide the demographic information on the service users who have received wellbeing and wraparound visits?", "Quarterly", "Created By", "Quarterly Case Study Attachment"}),
#"Added Index" = Table.AddIndexColumn(#"Reordered Columns2", "Index", 0, 1, Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Added Index",{{"Please can you provide demographic information on the service users who received CoL appointments, especially home visits?", "Demographic"}, {"Please provide the demographic information on the service users who have received wellbeing and wraparound visits?", "Demographic Test"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Custom", each List.First([Demographic])[filename]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
#"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows",{{"Custom", "Filename"}})
in
#"Renamed Columns3"