Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
New to Power BI and I am trying to look into my finances as a learning activity.
I have a years worth of bank statement data. Essentially all my purchases for a year, with a date column, a description column, and a money out column.
The description column featurs many hundreds of different descriptions, but a lot of them I want to group together to review spend at different vendors.
For example I often shop at Morrisons. Therefore I would like to group anything with the word "morrisons" (in any case) into a group.
Likewise with Amazon. Sometimes it appears in the description as "AMAZON", sometimes "amazon" sometimes "AMZN" etc. How can I group for example based on those 3 texts?
Each description has a load of blur with it, so can't just pivot column as every description is unique with unique reference numbers.
Therefore I need to set up the groups to just look for specific texts as mentioned above, and group them.
Hope this make sense - cheers
Solved! Go to Solution.
Hi @Anonymous ,
We can use the following steps to meet your requirement.
1. We need to create a new table that contains the group name.
2. Then we can use the merge query in this table. And select use fuzzy matching.
3. At last we can group by description and Date.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-zhenbw-msft @amitchandak @mahoneypat
Thank you all so much for taking the time to guide me here - appreciated 🙂
Hi @Anonymous ,
We can use the following steps to meet your requirement.
1. We need to create a new table that contains the group name.
2. Then we can use the merge query in this table. And select use fuzzy matching.
3. At last we can group by description and Date.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Refer if this can help
https://powerbi.microsoft.com/en-us/blog/tag/fuzzy-matching/
Please see this link to learn how to make groups column to do what you described.
https://exceleratorbi.com.au/grouping-in-power-bi-desktop-without-using-dax/
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |