'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Standardize Inconsistent PDF Columns in Excel Power Query
Question details
The user needs to import multi-page or multi-month PDF credit-card statements into Power Query but is facing issues with misaligned data.
- Product
- Microsoft Excel (Power Query)
- Device & OS
- not provided
- Scenario
- Importing PDF credit-card statements that contain varying table layouts and structures across different pages or billing months.
- Observed behavior
- Power Query imports varying numbers of columns and inconsistent data due to the structural differences in the PDF tables, making appending and analysis difficult.
Ensure you have the latest version of Excel installed, as Microsoft frequently updates the Power Query PDF connector to improve table recognition. Gather a few sample PDF statements to identify where the structural variations occur before building your query.
Apply Custom M Code and Consult Community Solutions
Because PDF structures can vary wildly, standardizing them usually requires combining files, applying manual query transformations, or using custom M code.
The Power Query PDF connector relies on spatial layout to identify columns. When a statement's layout shifts slightly from page to page, Power Query interprets this as a new column structure.
To resolve this, you will need to bypass the direct load and use the Power Query Editor to manually align the data.
Instead of clicking 'Load' when importing your PDF, select 'Transform Data' to open the Power Query Editor.
Utilize transformation tools such as 'Unpivot Other Columns', 'Remove Empty', or 'Fill Down' to normalize inconsistent rows before expanding your tables.
Check Microsoft's official Power Query PDF connector documentation for baseline capabilities and limitations regarding table extraction.
If the structure is too complex for standard interface buttons, post sample data (with sensitive information removed) on the Microsoft Power Query community forums for specialized M code help.
Try WPS Office for Your Spreadsheet and PDF Needs
While complex Power Query M-code transformations are specific to Microsoft Excel, WPS Office offers a lightweight, completely free alternative with built-in PDF to Excel conversion tools that handle everyday statements easily without coding.
- 1. Open WPS Office: Launch the WPS Office desktop application and navigate to the 'PDF' tab.
- 2. Select PDF to Excel: Click on the 'PDF to Excel' tool and choose the credit card statement you want to extract data from.
- 3. Convert and edit: Let the built-in conversion engine extract the tables into a clean spreadsheet, then edit or merge the data directly in WPS Spreadsheet.
Frequently Asked Questions
Why does Power Query split my PDF tables into multiple columns?
Power Query relies on invisible layout boundaries within the PDF. If the text spacing or margins change slightly across pages, the connector interprets this as a new column layout, resulting in misaligned or extra columns.
Can I merge inconsistent PDF tables automatically in Excel?
There is no single one-click solution if the columns differ fundamentally. You must use the Transform Data editor to standardize column names, remove nulls, and append the queries manually.
Does WPS Office have a tool like Power Query?
WPS Spreadsheet focuses on standard data analysis and includes powerful built-in tools like PivotTables and seamless PDF-to-Excel conversion, though it does not use the M-language Power Query engine.
