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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vaibhavreddy
Regular Visitor

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

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%

1 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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
McCow
Resolver III
Resolver III

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

 

Searsup to 1.08%
Kmartup to 2.7%
GNC12% 16%
Coach12% 14%
VerizonUp to $75
Rakuten10% 4%

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

@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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors