Hi,
I'm a newbie here and with Power Query. I'm trying to analyse my bank account data from CSV files downloaded each month from my bank. I've managed to get PowerQuery to work as I want it with one exception.
My Bank has a single field called description which contains data, a mix of alpha and numeric data to give the user a name/description of the transaction.
These can vary from the very simple eg
B/CARD VISA
to the more complex like
ABC BANK SAVER C , ABC BANK SAVER C , VIA MOBILE - PYMT , FP 06/06/22 10 , 12312654231232R
most point of sale transactions look like this
1017 20JUL22 C , EAST RIVER INN, TAUNTON GB
Unfortunately, there is no consistency on how companies enter their names, and different branches of the same company can often use different styling for their name eg
8556 23APR22 CD , Smith&Jones Store 1234 , EDINBURGH 4 GB
and
8556 23APR22 CD , Smith and Jones Store 5678 , PARIS 7 FR
Using Power query to split columns (Many /most have a comma delimiter some with a leading space, some without) generates 7 additional columns (based on my first 1000 records - so it could be more on the full dataset). With the single item I want (ie the company name) in anything from column 1 to column 7.
I'm hoping to be able to categorise these payments using a look-up table to put them into categories, such as Food & Drink, Entertainment, Sport etc
Is this an example that it is possible to come up with a method to extract the company name or is it simply too variable? I find it hard to believe that others have not sought a similar solution in the past, so I'm hoping that someone might have an answer.
Thanks for reading this far.
Colin
I'm a newbie here and with Power Query. I'm trying to analyse my bank account data from CSV files downloaded each month from my bank. I've managed to get PowerQuery to work as I want it with one exception.
My Bank has a single field called description which contains data, a mix of alpha and numeric data to give the user a name/description of the transaction.
These can vary from the very simple eg
B/CARD VISA
to the more complex like
ABC BANK SAVER C , ABC BANK SAVER C , VIA MOBILE - PYMT , FP 06/06/22 10 , 12312654231232R
most point of sale transactions look like this
1017 20JUL22 C , EAST RIVER INN, TAUNTON GB
Unfortunately, there is no consistency on how companies enter their names, and different branches of the same company can often use different styling for their name eg
8556 23APR22 CD , Smith&Jones Store 1234 , EDINBURGH 4 GB
and
8556 23APR22 CD , Smith and Jones Store 5678 , PARIS 7 FR
Using Power query to split columns (Many /most have a comma delimiter some with a leading space, some without) generates 7 additional columns (based on my first 1000 records - so it could be more on the full dataset). With the single item I want (ie the company name) in anything from column 1 to column 7.
I'm hoping to be able to categorise these payments using a look-up table to put them into categories, such as Food & Drink, Entertainment, Sport etc
Is this an example that it is possible to come up with a method to extract the company name or is it simply too variable? I find it hard to believe that others have not sought a similar solution in the past, so I'm hoping that someone might have an answer.
Thanks for reading this far.
Colin
