• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query m Code to Extract Call Number

Sanket Katdare

New Member
Hi There,

I am looking for Excel Power Query M Code which will to segregate case numbers in two different columns as per their type.
Also, there might be some free text available in few case numbers, wanted 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
CaseNo
183490-1000647
183461-1000754
183461-1001042
183461-1001069
183461-1001152
60-0032829170
60-0032745101
60-0032819228
60-0032827919
 
You can try (I'm assuming a call number starts with 60. Success with Excel/PQ depends highly on knowing all the possible data scenarios)
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Add_Record = Table.AddColumn(Source, "Record", each
                             let
                                         Nmbr = Text.BeforeDelimiter([Number], " ")
                                        ,isPhone = Text.StartsWith(Nmbr, "60")
                                       , Rec = if isPhone then [Case = null, No = Nmbr] else [Case = Nmbr, No = null]
                             in Rec),
    Expand_Record = Table.ExpandRecordColumn(Add_Record, "Record", {"Case", "No"}, {"Case", "No"})
in
    Expand_Record

81844
 
Back
Top