PQ - Cleanup data with several conditions

[sandy666]

Source dataExpected item no.

Carrier NameItem No.Finalised Clean Data

CMAAtlantis / 150965
150965

CMA150965 / Atlantis
150965

CMAAtlantis - 150965
150965

CMA
150965
150965

CMAOceania - 100258
100258

CMAOcean - 100258
100258

CMA
100258
100258

CMAOCN100258
100258

CMAOceania /100258
100258

EGTLEagle - DBSH261296DBSH261296

EGTLDBSH261296DBSH261296

EGTLDBSH261296 / EGTLDBSH261296

EGTLEag DBSH261296DBSH261296

EGTLTainer DBSH261296DBSH261296

EGTLGreenland DBSH280399DBSH280399

EGTLDBSH280399DBSH280399

EGTLGreen - DBSH280399DBSH280399

EGTLGL / DBSH280399DBSH280399



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 NameResCount

CMA150965
4

CMA100258
5

EGTLDBSH261296
5

EGTLDBSH280399
4


5101


// 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