I have a question and can't find the solution

[Emansuhh]

Hi all,

i work for a service organisation. we got a big list with orders numbers. they are logical build like: CS(support request) 23 (year) 01(month) 01(day) and than ticket nummer xxxx for example 0001.

so the compleet number is cs2301010001 i want to make a formule that see the logical sequence so i can sort on date and see all the ticket numbers and how many tickets i got each day.

thanks in advance,

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi42MjYwBEIDA0Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Date", each #date(Number.From("20" & Text.Range([Order Number],2,2)),Number.From(Text.Range([Order Number],4,2)),Number.From(Text.Range([Order Number],4,2))), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Ticket", each Text.End([Order Number],4),type text)
in
    #"Added Custom1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".