cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oyvindjohansen
New Member

Matching row from bank statement using wildcards

Hi

 

This is my first post here in the community and i'm pretty fresh to using Power BI.

What i'm trying to acomplish is using Power BI to get an overwiev of what and where my money goes 🙂

 

I have made a XLS export from my bank containing date, amount and location spent.

Problem is that the location spent often include additional info like ref number, date etc, and not only the store. I.eg "14032021 #125683 Joker Bortelid reftex" where "Joker Bortelid" is the acual store. Some times when the store name is long, this data will only contain part of the name.

 

To solve this i have created a reference table contain three columns: Store, chain, type.

This beeing in the example above: "Joker Bortelid"; "Joker", "Groceries"

 

So far so good, but now i need help to match using wildcar so that i can create visuals showing either how much i spend in each store, each chain or splitt by type.

 

Hope this made sense 🙂

 

Cheers

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @Oyvindjohansen 

To use fuzzy matching in Power Query, enable ‘Use fuzzy matching to perform the merge’ and input the Similarity threshold as 0.25.

 

Home>Combine>Merge Queries

v-cazheng-msft_0-1618969166581.png

 

 

Expand columns from Referance table except ‘Search’ column

v-cazheng-msft_1-1618969166584.png

 

 

The result looks like this:

v-cazheng-msft_2-1618969166586.png

 

 

To matching more accurately, you can create a Transform table which you can refer the part of Transformation Table in Fuzzy Matching in Power BI/Power Query.

 

Best Regards

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

5 REPLIES 5
v-cazheng-msft
Community Support
Community Support

Hi @Oyvindjohansen 

To use fuzzy matching in Power Query, enable ‘Use fuzzy matching to perform the merge’ and input the Similarity threshold as 0.25.

 

Home>Combine>Merge Queries

v-cazheng-msft_0-1618969166581.png

 

 

Expand columns from Referance table except ‘Search’ column

v-cazheng-msft_1-1618969166584.png

 

 

The result looks like this:

v-cazheng-msft_2-1618969166586.png

 

 

To matching more accurately, you can create a Transform table which you can refer the part of Transformation Table in Fuzzy Matching in Power BI/Power Query.

 

Best Regards

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Ashish_Mathur
Super User
Super User

Hi,

You may want to explore Fuzzy Lookup in the Query Editor.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thnx!

I will take a look

lbendlin
Super User
Super User

If you are new to Power BI you may want to find a slightly less complex starter project 🙂

 

Please provide sample data in usable format (not as a picture) and show the expected outcome.

Hi

 

Aiming for the stars you know!

 

 

Sample data:

 

DatoTypeBeskrivelseInn på kontoUt fra kontoOriginalt beløpValutaKurs
19.04.2021Varekjøp16.04 JOKER BORTELID  BORTELID SERAL -231,2-231,2NOK-
19.04.2021Varekjøp17.04 MENY VEN SENTRUMSVEGE VENNESLA -793-793NOK-
16.04.2021Varekjøp15.04 EXTRA Evje MAGNUS BARFO KRISTIANSAND -77,7-77,7NOK-

 

Referance table

SearchStoreChainType
Joker BortelidJoker BortelidJokerGroceries
MENY VENMeny VenneslaMenyGroceries
EXTRA EvjeExtra EvjeCoop ExtraGroceries

 

The result i hope to be able to generate:

DatoTypeBeskrivelseInn på kontoUt fra kontoOriginalt beløpValutaKursStoreChainType
19.04.2021Varekjøp16.04 JOKER BORTELID  BORTELID SERAL -231,2-231,2NOK-Joker BortelidJokerGroceries
19.04.2021Varekjøp17.04 MENY VEN SENTRUMSVEGE VENNESLA -793-793NOK-Meny VenneslaMenyGroceries
16.04.2021Varekjøp15.04 EXTRA EvjeMAGNUS BARFO KRISTIANSAND -77,7-77,7NOK-Extra EvjeCoop Extra

Groceries

 

Thank you

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors