Matching row from bank statement using wildcards

[Anonymous]

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

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

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

Expand columns from Referance table except ‘Search’ column

The result looks like this:

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]

Hi,

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

[Anonymous]

Thnx!

I will take a look

[lbendlin]

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.

[Anonymous]

Hi

Aiming for the stars you know!

Sample data:

Dato	Type	Beskrivelse	Inn på konto	Ut fra konto	Originalt beløp	Valuta	Kurs
19.04.2021	Varekjøp	16.04 JOKER BORTELID  BORTELID SERAL		-231,2	-231,2	NOK	-
19.04.2021	Varekjøp	17.04 MENY VEN SENTRUMSVEGE VENNESLA		-793	-793	NOK	-
16.04.2021	Varekjøp	15.04 EXTRA Evje MAGNUS BARFO KRISTIANSAND		-77,7	-77,7	NOK	-

Referance table

Search	Store	Chain	Type
Joker Bortelid	Joker Bortelid	Joker	Groceries
MENY VEN	Meny Vennesla	Meny	Groceries
EXTRA Evje	Extra Evje	Coop Extra	Groceries

The result i hope to be able to generate:

Dato	Type	Beskrivelse	Inn på konto	Ut fra konto	Originalt beløp	Valuta	Kurs	Store	Chain	Type
19.04.2021	Varekjøp	16.04 JOKER BORTELID  BORTELID SERAL		-231,2	-231,2	NOK	-	Joker Bortelid	Joker	Groceries
19.04.2021	Varekjøp	17.04 MENY VEN SENTRUMSVEGE VENNESLA		-793	-793	NOK	-	Meny Vennesla	Meny	Groceries
16.04.2021	Varekjøp	15.04 EXTRA EvjeMAGNUS BARFO KRISTIANSAND		-77,7	-77,7	NOK	-	Extra Evje	Coop Extra	Groceries

Thank you