Importing Credit card statement to power query in PDF format

Question

When I load my credit card statement that is in PDF format into power query I find there is absolutely no consistency in the number of columns or the information on each table/page. How do I clean up one month so that I can use the same process for other months? The inconsistency from table to table and month to month is large. Some tables have 3 columns, some 4, some 5. The column names are also inconsisitent. So what happens in one month may not happen in the next month. Some tables have only a couple of rows of information others have a great deal more, this means each month has a large number of tables. As these are old statements they can only be downloaded as PDF.

Thanks,

Eqa

anonymous · Answer

Hi&nbsp;Eqa&nbsp;,
&nbsp;
Try putting all the PDF files in a folder and then using PBI to connect to the folder.
Select to Combine &amp; Transform Data.
Then we need to make some changes to the automatically generated M code.
Here, we drop the header of each table and then transpose the table.

For ease of management, I've created a new Enter table here to hold the required find and replace values:

Again, another new custom function is created for ease of management:

Next, start going back to the combined table and start replacing column names:

Get all column names.

Expand all columns.

PBIX and source files are attached for reference.
&nbsp;
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

eqa · Answer

Thank you so much for your help. As I'm not PBI savy at all, I'm using excel power query to get the data from the folder, I don't believe this will make any difference. Having said that, how do I get the equivalent of your screen shot, where you are showing Source name and transform file listing the tables? The 1st screen shot.

Thanks again,

Eqa

anonymous · Answer

Hi&nbsp;Eqa&nbsp;,
&nbsp;
Select to Combine &amp; Transform Data.

Click OK.

And then we'll get to those. We'll then use that as a basis for modification.

I have uploaded an Excel file containing the steps for your reference.
&nbsp;
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum