Merging fields and grouping within fields

[Anonymous]

Novice with Power BI / Power Query but familiar with Tableau Prep / Desktop. Each has its pros and cons, in this instance I need to be able to use Power BI for this. Any help is appreciated, it just doesn't seem intuitive to me yet how it's laid out. Searched the forum and haven't come across the answers yet. 

1.) In Prep I can merge two fields (columns) with similar fields and it creates one column and I won't get a duplicate answer in the cells (like ABE ABE within Power Query). How is this done in Power BI? I haven't seen a clear answer on this. 
Example using airport codes: 

Origin Station	Departure Station
ABE	ABE
SCK	SCK
JFK	JFK
PNS	PNS
DFW	DFW
CLT	CLT

The Excel sheet I'm given to report on combines 3 airlines worth of information in to one sheet so I'm not joining the data, just using what I'm given. I'm just looking to see where the merge in PQ is like in Prep, if any, where it'll combine without issue. 

2.) In Prep I'm able to add a "Clean" step where I'm able to group data easily within the same fields (columns). I need to easily be able to combine the areas to create regions for areas of responsibility. What is the equivalent in Power Query to create groups easily (especially the renaming throughout the entire row below that isn't just 'replace values')? 

Example: 

In the pictures below from Tableau Prep I can hold control, select the items to group, group them, and change the name which is reflected then in the table below it where I combined the North, Central, South Americas and the Carribean to create the "Americas" group. I need to be able to do this in Power Query.

field variables can be grouped easilyFinal Product - and can change the name for the group with a double click

[KNP · Accepted solution]

Hi @Anonymous, 

If the parent grouping isn't available in the source data then you could add a conditional column.

That has potential to not be a very dynamic solution depending on how changable the data is, so be careful if you go down this path.

The other way to do it outside of Power Query is to use the Group function. 

In the field list, right click on the field you want to group and click 'New Group'. You will then get the dialogue shown below which I think will be familiar to you.

Hope that helps.

Kim

[v-yingjl · Accepted solution]

Hi @Anonymous ,

1)

You can depulicate your table to create a transformationtable with the [From] column and [To] column and create a custom column in the source table to do the fuzzy match:

= Table.AddFuzzyClusterColumn(
        #"Changed Type",
        "Origin Station",
        "New",
        [
            IgnoreCase = true,
            IgnoreSpace = true,
            TransformationTable = Transformationtable
        ]
    )

Keep this new column and remove duplicates in it:

You can also refer this articles about fuzzy match:

1. Fuzzy Matching in Power BI and Power Query; Match based on Similarity Threshold 
2. Table.AddFuzzyClusterColumn 

2) You can refer @ KNP suggestion to do the group in power query.

 Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KNP · Accepted solution]

Hi @Anonymous, 

If the parent grouping isn't available in the source data then you could add a conditional column.

That has potential to not be a very dynamic solution depending on how changable the data is, so be careful if you go down this path.

The other way to do it outside of Power Query is to use the Group function. 

In the field list, right click on the field you want to group and click 'New Group'. You will then get the dialogue shown below which I think will be familiar to you.

Hope that helps.

Kim