Forum Discussion
Equivalent function of Tableau : group and replace
Hello PBI Community!
Is there a function in Power BI / Power Query to perform a similar function as below?
There is a column with multiple versions of the same entry. And the aim is to categorise these into one category (by defining a logic such as - looking at the first 5 characters etc.
[Example column]= Sample, samplee, sampel1, sampleer, Apple, apple, appler, appel etc..
In Tableau, it seems possible to semi-manually group and replace. https://www.tableau.com/products/prep
Hi misul
function of Tableau : group and replace is similar to function of Power BI : Binning and Grouping.
Refer to this article, you could use "group" to categorise multiple items into one category.
Or if you want to do some data transformations, like grouping rows by number of fields, you could consider
Best Regards
Maggie
4 Replies
You can do this via grouping in Power BI. You could semi-automate this by creating a calculated column that uses FIND or SEARCH to categorize rows.
Hi,
Can you briefely describe what do you mean by that? it is good to have screen shot for better understanding.
Thanks,
Sandip Ghosh
So, if you have a column that you want to do grouping on, in the Fields area, click the ellipses next to the column name and choose "New Group". You will get a dialog like this:
You can use this dialog to manually group values within the column so that all of those different columns appear in your groups when you use the group that is created instead of the column in your visuals.
Now, another way of doing this is to create a new column in your model that essentially does the grouping. This may work better for your scenario. You can write a DAX function to do this, perhaps something like the following:
Column = SWITCH( TRUE(), IF(SEARCH("P",[Category],,FALSE),TRUE,FALSE),"Group1", IF(SEARCH("D",[Category],,FALSE),TRUE,FALSE),"Group2", "Group3" )Then you can use this calculated column in your visual and your information will be grouped as you define it in the formula.
Hi misul
function of Tableau : group and replace is similar to function of Power BI : Binning and Grouping.
Refer to this article, you could use "group" to categorise multiple items into one category.
Or if you want to do some data transformations, like grouping rows by number of fields, you could consider
Best Regards
Maggie
