Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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.
Thank you for the idea! There is so much to PowerBI - so do greatly appreciate the support offered thru this forum!
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
Hey Frank!
Thank you very much - quick and elegant solution. Appreciate your rapid response!
This forum is great!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |