I have two column store & cashback needs maximum percentage as a New Column

[vaibhavreddy]

I have two column store and cashback 

Like example

Store               CashBack

Walmart           5%12%

BestBuy           up to 3.08%

I need Results like this Maximum Cashback and need to get rid of "up to"

Store               CashBack

Walmart           12%

BestBuy           3.08%

[McCow]

Hi @vaibhavreddy

the solution with ""up to"" seems me pretty simple, but for the first request i need a source. Just 2-3 rows of the source table must be ok.

Can you publish here?

Best regs

[vaibhavreddy]

Sears	up to 1.08%
Kmart	up to 2.7%
GNC	12% 16%
Coach	12% 14%
Verizon	Up to $75
Rakuten	10% 4%

[McCow]

Hi @vaibhavreddy

try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk5NLCpW0lEqLVAoyVcw1DOwUFWK1YlW8s5NLCqBixvpmUOE3f2cgYKGRqoKhmYQEef8xOQMmJgJRCwstSizKj8PKBoK1q5ibgoWD0rMLi1JBYkbGqgqgBTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter("% ", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Split Column by Delimiter", "up to", each Text.AfterDelimiter([Column2.1], " ", 1), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Text After Delimiter", "Custom", each if Text.Contains([Column2.1], "p to") then null else [Column2.1]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column","%","",Replacer.ReplaceText,{"Column2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column2.2", type number}, {"Custom", type number}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Column2.2] > [Custom] then [Column2.2] else if [Column2.2] < [Custom] then [Custom] else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column1", {{"Custom.1", null}}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Column2.1", "Column2.2", "Custom"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if [Custom.1] <> null then [Custom.1]&"%" else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Custom.1"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Removed Columns1", "Custom.1", each if [Custom] = null then [up to] else [Custom], type text),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column2",{"up to", "Custom"})
in
    #"Removed Columns2"

of course it is ugly code, but explain a possible steps of converting your mix data ("%" and "$" together). 95% composed from interface only 🙂

and feel free to ask back

[v-chuncz-msft · Accepted solution]

@vaibhavreddy,

You may use space as a delimiter to split column into rows in Query Editor, then use RANKX Function to add a calculated column in DAX.