Reply
Frequent Visitor
Posts: 14
Registered: ‎10-30-2018
Accepted Solution

Extract Nth number of a cell

Hi,

 

I have column with values, for example:

1.5 2.5

1-5 2-5

1/4 2/4

1. - 1. 2. - 3.

1 5 2 4

etc.

 

And I would like to return the second and fourth number/digit of the cell's value. Would it be possible to do that in the querry editor, with a formula or with another way?

 

Regards,

Edgars


Accepted Solutions
Community Support Team
Posts: 1,997
Registered: ‎07-10-2018

Re: Extract Nth number of a cell

Hi @EdgarsCK,

 

I made one sample for your reference. Please check the output as the picture as below.

 

1.PNG

 

M code in power query for your refernce.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzVTDSM1WK1QGydYFsXShb30TBSN8EwtZT0FUAEkYg2lgPIqYAVKsAlI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Split Column by Position" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByRepeatedLengths(1), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Custom.2", type text}, {"Custom.4", type text}}, "en-US"),{"Custom.2", "Custom.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type number}})
in
    #"Changed Type2"

For more details, please check the pbix as attached.

 

Regards,

Frank

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

View solution in original post

Attachment

All Replies
Frequent Visitor
Posts: 14
Registered: ‎10-30-2018

Re: Extract Nth number of a cell

I might have found a solution myself. I found a formula in one of the previous related posts:

Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))

This one extracts only numbers without any other symbols. So I used this one and them used Text.Range formula to get the second and fourth value.

 

But maybe there's still a shorter way how it can be done? 

Super User
Posts: 2,850
Registered: ‎09-27-2017

Re: Extract Nth number of a cell

@EdgarsCK

 

This might be another way.  But its not shorter by any means Smiley Tongue

 

Text.Combine(
List.RemoveItems(Text.ToList([Column1]),
List.RemoveItems(Text.ToList([Column1]),{"0".."9"})))

 

Community Support Team
Posts: 1,997
Registered: ‎07-10-2018

Re: Extract Nth number of a cell

Hi @EdgarsCK,

 

I made one sample for your reference. Please check the output as the picture as below.

 

1.PNG

 

M code in power query for your refernce.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzVTDSM1WK1QGydYFsXShb30TBSN8EwtZT0FUAEkYg2lgPIqYAVKsAlI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Split Column by Position" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByRepeatedLengths(1), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Custom.2", type text}, {"Custom.4", type text}}, "en-US"),{"Custom.2", "Custom.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type number}})
in
    #"Changed Type2"

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Community Support Team
Posts: 1,997
Registered: ‎07-10-2018

Re: Extract Nth number of a cell

Hi @EdgarsCK,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 14
Registered: ‎10-30-2018

Re: Extract Nth number of a cell

@v-frfei-msft Yes, thank you for your help!

 

Br,

Edgars