Extract records from Excel file

[micha_a]

Is it possible to extract data from an Excel file that is stored in a list? I am connecting to the data source via an API, and in one of the columns, the user is attaching a spreadsheet with more information. I am trying to extract that information stored under the filename:Demographics of Service Users Form_AUKW_Q1.xlsx via Power Query, but not sure how.

[Anonymous]

Hi @micha_a 

Yes it's possible. It seems this column contains a list of records. Assume that there is only one file attached on each row so there should be only one record in each list. So you can add a custom column with below code to extract the filename from the first record in the list. 

List.First([Demographic])[filename]

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[micha_a]

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"

[Anonymous]

@micha_a Is the file content in the same record or in another column? The key is to extract the file content first, just like how we get the filename. The file content usually is binary type. Then you can use Excel.Workbook function to return the content inside an Excel workbook. 

I'm not able to use your M code, perhaps you can show a screenshot of where a file is located, then I can try to provide some code to extract it. 

Best Regards,
Jing

[NaveenKumar0]

Given your scenario, the steps to access and extract data from an Excel file stored in a list via an API typically involve:

> Connect to the API: Home tab → New Source → Web → provide the API URL.
> Authenticate (if required): Follow the prompts to authenticate with the API.
> Navigate the JSON response: Locate the column with the file, and extract the URL or binary data for the Excel file.
> Connect to the Excel file: If you have a URL, use New Source → Web again with the file's URL. If it's binary data, use the "From Binary" transformation.
> Extract the needed data: Select the relevant sheet and data range within the Excel workbook.

if that helped, please upvote me!!