Customer group split based on stores

[Justas4478]

Hi, I have customer group that holds stores for UK & NI in the same group.

I want to split it in to two groups Stores NI and whats left Stores UK.
The problem is NI stores get added and removed every time so I cant choose stores manualy and I need some way to define NI stores in order to create new group.
Here is example of some stores:

As you see majority of stores have 'NI' in their name that could be something that can be used as filter to group them.
Unfortunately to that there is one store that does not have NI in it (could be chance that it will be moved soon), and other two have 'NI' in the middle of the name.
Let me know if anyone has any ideas how to solve it.
If there is solution using M code.
Thanks

[ajohnso2]

If i were you I would be looking to have a stores Dimension table where you can assign the correct NI/UK flag before loading into your model and then join this to your fact table on store number. If this is not possible @SamWiseOwl suggestion is probably the way to go however you will have to account for future variations of your search string.

[SamWiseOwl]

Hi @Justas4478 

You could creater a Calculated Column in the Table view:
Location =
If( 

CONTAINSSTRING([Store Name], " NI") , "NI", "UK")

[Justas4478]

@SamWiseOwl Is this going to as well check other versions of NI for example:
'Ni', 'ni' or if they appear inside store names like this, 'Keniworth'?
Since these are my concerns if they will get considered as part of 'NI' group.

[SamWiseOwl]

It isn't case sensitive.

Note I put " NI" the space infront means it can't be in Keniworth as it won't have a space in front of it.

[Justas4478]

@SamWiseOwl I think it could work but I am not sure how it integrates with other groupings.
Here is full list of groupings:

Beause of that it might have to be M code solution.

[SamWiseOwl]

Hi @Justas4478 

Could you not add this new column into the slicer.

They can then expand the Stores UK & NI and tick which they wanted.

[Justas4478]

@SamWiseOwl If I understand correctly is it not going to group all the aother stores under UK group from other customer groups?

[SamWiseOwl]

You could inclue them in the test.

New Column =

Switch(
True()
,[Customer Group Description] <> "Stores UK & NI" --if not this

,[Customer Group Description] --return original value

,ContainsString([Store Name], " NI")

,"Stores NI"

,"Stores UK"
)

[Justas4478 · Accepted solution]

@SamWiseOwl I think I manage to find solution in M code:
Table.ReplaceValue(#"Renamed Columns1",each [Customer Group Description],each if Text.Contains([Store Name], " NI") or [Store Number] ="1378" then "Stores NI" else [Customer Group Description],Replacer.ReplaceText,{"Customer Group Description"})

[Anonymous]

Hi @Justas4478 , hello all, thank you for your prompr reply!

I'm glad to hear you found a solution and thank you for sharing it.

Please remember to accept your solution as answer.

It will do great help to those who meet the similar question in this forum.

Thanks again for your contribution.