Importing Credit card statement to power query in PDF format

[Eqa]

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

[Eqa]

Can I clean this up as it is much messier than your example?

[Eqa]

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]

Hi @Eqa ,

Select to Combine & 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.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  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. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Eqa]

Here is a screenshot showing that my info is much messier than yours. Can I clean this up?

[Anonymous]

Hi @Eqa ,

Try putting all the PDF files in a folder and then using PBI to connect to the folder.

Select to Combine & 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.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  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. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum