PQ - Cleanup data with several conditions

Source data			Expected item no.
Carrier Name	Item No.		Finalised Clean Data
CMA	Atlantis / 150965		150965
CMA	150965 / Atlantis		150965
CMA	Atlantis - 150965		150965
CMA	150965		150965
CMA	Oceania - 100258		100258
CMA	Ocean - 100258		100258
CMA	100258		100258
CMA	OCN100258		100258
CMA	Oceania /100258		100258
EGTL	Eagle - DBSH261296		DBSH261296
EGTL	DBSH261296		DBSH261296
EGTL	DBSH261296 / EGTL		DBSH261296
EGTL	Eag DBSH261296		DBSH261296
EGTL	Tainer DBSH261296		DBSH261296
EGTL	Greenland DBSH280399		DBSH280399
EGTL	DBSH280399		DBSH280399
EGTL	Green - DBSH280399		DBSH280399
EGTL	GL / DBSH280399		DBSH280399

• Operator inputs different variants of data which makes data collection messy
• Clean data represents what should have been input for the Item No. highlighted in yellow
• There is a total of 18 variants, the data should be clean like expected item no. column
• The original data that I need to use will be over hundreds of variations so manually doing this will not be possible
• Is there a way for me to see how many variants does CMA and EGTL have each?
• I will also create a chart to monitor on a weekly basis to see if the number of variants has gone up or down

Result
Carrier Name	Res	Count
CMA	150965	4
CMA	100258	5
EGTL	DBSH261296	5
EGTL	DBSH280399	4

Attachment 5101

Code:

---

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.TransformColumnTypes(Source,{{"Item No.", type text}}),
    IF = Table.AddColumn(Text, "Res", each if [Carrier Name] = "CMA" then Text.Combine(List.RemoveItems(Text.ToList([#"Item No."]),{"a".."z", "A".."Z", "/", "-", " "})) else if [Carrier Name] = "EGTL" then Text.Combine(List.RemoveItems(Text.ToList([#"Item No."]),{"a".."z", "A","C","E".."G","I".."R","T".."Z", "/", "-", " "})) else null),
    Grp = Table.Group(IF, {"Carrier Name", "Res"}, {{"Count", each Table.RowCount(_), type number}})
in
    Grp

---