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

[Dellis81]

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.

Range	Low	High
1,2,3,4,5	1	5
7-14	7	14
1-14	1	14
1-15	1	15
1,2,3,4,5,6	1	6

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

[Ashish_Mathur]

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.

[Dellis81]

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

[FrankAT · Accepted solution]

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

Regards FrankAT

[Dellis81]

Hey Frank! 

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

This forum is great!