Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dellis81
Continued Contributor
Continued Contributor

PQ: Parsing text string, with numbers, finding min and max

Hello!

 

I am needing a way within PowerQuery to extract a string (of numbers) and return the min and max.  Eample below - with the Range column within my dataset, and Low/High (red) are desired end result.

RangeLowHigh
1,2,3,4,515
7-14714
1-14114
1-15115
1,2,3,4,5,616

 

I have tried the Text.Split function - but not getting very far.   As always - your help is greatly appreciated!

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

Hi @Dellis81 

follow these steps:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQx0jHWMdExVYrViVYy1zU0ATMMkRgQKbhCHTOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Range = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Range", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","-",",,",Replacer.ReplaceText,{"Range"}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Replaced Value", "Min", each Text.BeforeDelimiter([Range], ","), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Max", each Text.AfterDelimiter([Range], ",", {0, RelativePosition.FromEnd}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text After Delimiter",{{"Min", Int64.Type}, {"Max", Int64.Type}})
in
    #"Changed Type1"

 

Figure

21-03-_2020_16-31-43.png

 

Regards FrankAT

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You can solve this question by using Add Column > Column from Examples > From Selection.  Type the expected result in the first 2 cells and the others should auto populate.  Do the same for another column.  This is the M code that gets auto generated.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Range", type text}}),
    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Range], 1), type text),
    #"Added Custom Column" = Table.AddColumn(#"Inserted First Characters", "Custom", each let splitRange = List.Reverse(Splitter.SplitTextByDelimiter(",", QuoteStyle.None)([Range])) in Text.End(splitRange{0}?, 2), type text)
in
    #"Added Custom Column"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for the idea!   There is so much to PowerBI - so do greatly appreciate the support offered thru this forum!

 

 

FrankAT
Community Champion
Community Champion

Hi @Dellis81 

follow these steps:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQx0jHWMdExVYrViVYy1zU0ATMMkRgQKbhCHTOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Range = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Range", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","-",",,",Replacer.ReplaceText,{"Range"}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Replaced Value", "Min", each Text.BeforeDelimiter([Range], ","), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Max", each Text.AfterDelimiter([Range], ",", {0, RelativePosition.FromEnd}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text After Delimiter",{{"Min", Int64.Type}, {"Max", Int64.Type}})
in
    #"Changed Type1"

 

Figure

21-03-_2020_16-31-43.png

 

Regards FrankAT

Dellis81
Continued Contributor
Continued Contributor

Hey Frank! 

 

Thank you very much - quick and elegant solution.   Appreciate your rapid response!

This forum is great!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.