Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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%
Solved! Go to Solution.
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.
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
Sears | up to 1.08% |
Kmart | up to 2.7% |
GNC | 12% 16% |
Coach | 12% 14% |
Verizon | Up to $75 |
Rakuten | 10% 4% |
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |