cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EdgarsCK Regular Visitor
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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.
5 REPLIES 5
EdgarsCK Regular Visitor
Regular Visitor

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
Super User

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
Community Support Team

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.
Community Support Team
Community Support Team

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.
EdgarsCK Regular Visitor
Regular Visitor

Re: Extract Nth number of a cell

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

 

Br,

Edgars

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 121 members 1,479 guests
Please welcome our newest community members: