Forum Discussion
Extract First Value in Integer
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
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
2 Replies
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
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
