Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
colmac
Regular Visitor

Categorising after Power Query Import

I am analysing my bank account data, which as downloaded comes form the bank in a fairly messy condition.

 

I've used Power Query to get all my monthly statements (in a single folder) and manage the basic transforming. That leaves me with perfectly clean data apart from the payee, which includes multiple similar looking entries - for example, I have so far discovered over 20 different entries which include Amazon, in one format or another.

 

I need to add categories to make sense of the eventual pivot table I'm going to build, and I intend to have a category and a sub-category. I'm looking at something like

 

Utility      Gas
Utility      Electricity
Entertainment      Cinema
Entertainment      Netflix
Misc       Amazon
Misc       Ebay

etc

 

Since the data is in such a variable format (and presumably future months will bring more variations), I believe a look-up table will be unworkable, but I hope I can manage the categorisation manually (I've only about 50 to 60 new entries per month) which should be reasonably easy to categorise after I sort the payee aphabetically to get "similar" entries close to each other.

 

My first trial was to add 2 extra columns to my table in Excel, manually coding the categories (not too massive a job as I only working with 1 year of data, and many entries are similar).

 

Being a Power Query newbie I'm nervous about this. I did a partial test entering about 50 categories, then added an extra month to my data folder to discover that the refresh imported the new data in a different order as I hadn't reset the order in the excel sheet, and so the columns I had added in Excel were now randomly allocated to incorrect imported data. Forgetting to reorder the sheet before a refresh will be an easy mistake to repeat, so I'm not happy with this option.

 

Can anyone suggest a better solution please. As far as I can see, doing it inside Power query does not look possible, but Finding answers is not easy in Google.

 

I hope this rambling note is not too long, but gives enough info to help.

 

Thanks

 

Colin
Colin

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @colmac ,

 

Almost anything is technically possible within Power Query (as far as data transformation/categorisation is concerned) given enough time and appetite.

You'll need to provide a comprehensive (and copyable) sample of text fields that you want to be able to categorise. Ideally, these would also include examples of how you would expect each value to be categorised.

Without this we're working blind, which is impossible.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




OK thanks, I'll leave it, I can cope manually

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors