Excel Power Query m Code to Extract Call Number

[sanket_sk]

Hi There,

I am looking for Excel Power Query M Code which will Segregate Call numbers in two different columns as per their type.
Also there might be some free text available in few case numbers, need to remove those unwanted characters and extract correct call number

Sample Input Table

Number
183461-1000754
183461-1001042
183461-1001069
183461-1001152
183490-1000647 Complaint
60-0032745101
60-0032819228
60-0032827919
60-0032829170 Demo

Expected Output

Case	No
183490-1000647
183461-1000754
183461-1001042
183461-1001069
183461-1001152
	60-0032829170
	60-0032745101
	60-0032819228
	60-0032827919

 

[jdellasala]

Here's the result:

Book1
	A	B	C	D	E
1	Calls		CallReport
2	Number		Number	Case	No
3	183461-1000754		183461-1000754	183461-1000754
4	183461-1001042		183461-1001042	183461-1001042
5	183461-1001069		183461-1001069	183461-1001069
6	183461-1001152		183461-1001152	183461-1001152
7	183490-1000647 Complaint		183490-1000647 Complaint	183490-1000647
8	60-0032745101		60-0032745101		60-0032745101
9	60-0032819228		60-0032819228		60-0032819228
10	60-0032827919		60-0032827919		60-0032827919
11	60-0032829170 Demo		60-0032829170 Demo		60-0032829170
Sheet1

And here's the M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Calls"]}[Content],
    AddedCaseColumn = Table.AddColumn(Source, "Case", each if Text.StartsWith([Number], "1") then [Number] else null),
    SplitColumnBySpace1 = Table.SplitColumn(AddedCaseColumn, "Case", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Case", "Case.2"}),
    RemovedCase2 = Table.RemoveColumns(SplitColumnBySpace1,{"Case.2"}),
    AddedNoColumn = Table.AddColumn(RemovedCase2, "No", each if Text.StartsWith([Number], "60-") then [Number] else null),
    SplitColumnBySpace2 = Table.SplitColumn(AddedNoColumn, "No", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"No", "No.2"}),
    RemovedNo2 = Table.RemoveColumns(SplitColumnBySpace2,{"No.2"}),
    ReplacedNulls = Table.ReplaceValue(RemovedNo2,null,"",Replacer.ReplaceValue,{"Case", "No"}),
    ChangedType = Table.TransformColumnTypes(ReplacedNulls,{{"Number", type text}, {"Case", type text}, {"No", type text}}),
    CleanedText = Table.TransformColumns(ChangedType,{{"Case", Text.Clean, type text}, {"No", Text.Clean, type text}}),
    TrimmedText = Table.TransformColumns(CleanedText,{{"Case", Text.Trim, type text}, {"No", Text.Trim, type text}})
in
    TrimmedText

Note that I manually remove spaces from step names, and the last 2 steps (CleanedText and TrimmedText) are probably not needed, but don't hurt.

This was all done right through the Interface and very simple and basic. I suggest watching either Mr. Excel's Power Query playlist, or Mike's playlist Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel on his ExcelIsFun channel. I prefer Mike's because he provides before and after files. Learning just the basics (which would have taken little time for this) can save you hours of time.

 

[sanket_sk]

Here's the result:

Book1
	A	B	C	D	E
1	Calls		CallReport
2	Number		Number	Case	No
3	183461-1000754		183461-1000754	183461-1000754
4	183461-1001042		183461-1001042	183461-1001042
5	183461-1001069		183461-1001069	183461-1001069
6	183461-1001152		183461-1001152	183461-1001152
7	183490-1000647 Complaint		183490-1000647 Complaint	183490-1000647
8	60-0032745101		60-0032745101		60-0032745101
9	60-0032819228		60-0032819228		60-0032819228
10	60-0032827919		60-0032827919		60-0032827919
11	60-0032829170 Demo		60-0032829170 Demo		60-0032829170
Sheet1

And here's the M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Calls"]}[Content],
    AddedCaseColumn = Table.AddColumn(Source, "Case", each if Text.StartsWith([Number], "1") then [Number] else null),
    SplitColumnBySpace1 = Table.SplitColumn(AddedCaseColumn, "Case", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Case", "Case.2"}),
    RemovedCase2 = Table.RemoveColumns(SplitColumnBySpace1,{"Case.2"}),
    AddedNoColumn = Table.AddColumn(RemovedCase2, "No", each if Text.StartsWith([Number], "60-") then [Number] else null),
    SplitColumnBySpace2 = Table.SplitColumn(AddedNoColumn, "No", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"No", "No.2"}),
    RemovedNo2 = Table.RemoveColumns(SplitColumnBySpace2,{"No.2"}),
    ReplacedNulls = Table.ReplaceValue(RemovedNo2,null,"",Replacer.ReplaceValue,{"Case", "No"}),
    ChangedType = Table.TransformColumnTypes(ReplacedNulls,{{"Number", type text}, {"Case", type text}, {"No", type text}}),
    CleanedText = Table.TransformColumns(ChangedType,{{"Case", Text.Clean, type text}, {"No", Text.Clean, type text}}),
    TrimmedText = Table.TransformColumns(CleanedText,{{"Case", Text.Trim, type text}, {"No", Text.Trim, type text}})
in
    TrimmedText

Note that I manually remove spaces from step names, and the last 2 steps (CleanedText and TrimmedText) are probably not needed, but don't hurt.

This was all done right through the Interface and very simple and basic. I suggest watching either Mr. Excel's Power Query playlist, or Mike's playlist Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel on his ExcelIsFun channel. I prefer Mike's because he provides before and after files. Learning just the basics (which would have taken little time for this) can save you hours of time.

Thanks A Lot!!!
This is working exactly as expected

Thanks & regards,
Sanket

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Power Query m Code to Extract Call Number
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[lrobbo314]

Just for fun, here is another way.

PQX
	A	B	C	D	E
1	Number		Number	Case	No
2	183461-1000754		183461-1000754	183461-1000754
3	183461-1001042		183461-1001042	183461-1001042
4	183461-1001069		183461-1001069	183461-1001069
5	183461-1001152		183461-1001152	183461-1001152
6	183490-1000647 Complaint		183490-1000647 Complaint	183490-1000647
7	60-0032745101		60-0032745101		60-0032745101
8	60-0032819228		60-0032819228		60-0032819228
9	60-0032827919		60-0032827919		60-0032827919
10	60-0032829170 Demo		60-0032829170 Demo		60-0032829170
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UNQ = List.Zip({List.Distinct(List.Transform(Source[Number],each Text.Middle(_,0,1))),{"Case","No"}}),
    AddColumns = List.Accumulate(UNQ,Source,(s,c)=> Table.AddColumn(s,c{1},each if Text.Middle([Number],0,1)=c{0} then Text.Split([Number]," "){0} else null))
in
    AddColumns