Extract First Value in Integer

[Anonymous]

Hi Experts. I would need your help in one my scenario where I want to extract the first integer of my account number and based on that I need to calculate the sum. Please find the below dataset as your reference.

If you see the data I have different account numbers starting with 1,2, and so on(let's say 16056 and 22021 ...) likewise I have series up to 9. So in my case I want to extract 1 t0 4 series in one column and remaining which is 5 t0 9 in another column. Eventually I want to show the sum of value for 1 t0 4 in one card and 5 to 9 in another card. 

Could any one help on this problem?

Best Regards,

Srihari

[v-deddai1-msft]

Hi @Anonymous ,

Why you need to extract 1-4 and 5-9 to different columns, you can extract them in one column and for card you can use the following two measure:

sun1-4 = CALCULATE(SUM('Table'[Value]),FILTER('Tale','Table'[fn] in {1,2,3,4}))
sum5-9 = CALCULATE(SUM('Table'[Value]),FILTER('Tale','Table'[fn] in {5,6,7,8,9}))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[BA_Pete · Accepted solution]

Hi @Anonymous ,

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc25DcAwDEPRXVyn0EFdsxjef40YUtI9gPjg3itBjnWeKxaOFjuZt9xg3BIhGamVV6uEYooEg2Ylt1FW4S8UUyCUprAMaCtM1L8P07ueFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccountNumber = _t]),
    #"Added Custom" = Table.AddColumn(Source, "1-4", each if Text.Start([AccountNumber], 1) = "0" or
Text.Start([AccountNumber], 1) = "1" or
Text.Start([AccountNumber], 1) = "2" or
Text.Start([AccountNumber], 1) = "3" or
Text.Start([AccountNumber], 1) = "4"
then Text.Start([AccountNumber], 1) else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "5-9", each if Text.Start([AccountNumber], 1) = "5" or
Text.Start([AccountNumber], 1) = "6" or
Text.Start([AccountNumber], 1) = "7" or
Text.Start([AccountNumber], 1) = "8" or
Text.Start([AccountNumber], 1) = "9"
then Text.Start([AccountNumber], 1) else null)
in
    #"Added Custom1"

This may not be the most elegant solution, but it's the quickest.

Pete