Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
100 | |
83 | |
76 | |
65 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |