Forum Discussion
Transform multiple sample page / query
I'm trying get data from pdf files. Its first page data is slightly different from other pages. First I filtered 1st page and did transformation in sample transformation query. After that from there ...
Here are the general steps:
2. Use the "Binary.Combine" function to combine the contents of all the PDF files into a single binary column.
3. Use the "Pdf.Tables" function to extract the tables from the combined binary column.
4. Use any necessary transformations to clean and reshape the data.
- Use the "Folder.Files" function to get a list of all the PDF files in the folder.
Here is an example M code to get you started:
let // Step 1: Get list of PDF files Source = Folder.Files("C:\Path\To\PDF\Folder"), PDFFiles = Table.SelectRows(Source, each [Extension] = ".pdf"), // Step 2: Combine PDF files into single binary column CombinePDFs = Table.AddColumn(PDFFiles, "Contents", each Binary.Combine({[Content]})), // Step 3: Extract tables from PDF files ExtractTables = Table.AddColumn(CombinePDFs, "Tables", each Pdf.Tables([Contents], [Name])), ExpandedTables = Table.ExpandTableColumn(ExtractTables, "Tables", {"Data", "Columns"}, {"Data", "Columns"}), // Step 4: Clean and reshape data as needed // ... // Combine all tables into a single table CombinedTables = Table.Combine(ExpandedTables[Data]) in CombinedTablesThis code assumes that all of the PDF files in the folder have tables on their pages.
If some of the files do not have tables or have different structures, you may need to add additional logic to handle those cases.