Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a column with various prices and blank cells, I want to replace all the different $ amounts to a 'rebate' text and then all the blank cells to 'no rebate' text. I could indiviudal search the $ amounts but there thousands of lines that could all be different so searching for a soluation that will avoid having to generate a list of all the unique values. Ex. If blank replace to 'no rebate', if not replace to 'rebate'?
Thanks,
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIztrRUitWJVlIAk4amYMrUQM8QSRRCGuiBlMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Original", type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Original],each if [Original]=null then "No Rebate" else "Rebate",Replacer.ReplaceValue,{"Original"})
in
#"Replaced Value"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIztrRUitWJVlIAk4amYMrUQM8QSRRCGuiBlMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Original", type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Original],each if [Original]=null then "No Rebate" else "Rebate",Replacer.ReplaceValue,{"Original"})
in
#"Replaced Value"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Great, thanks!
yes, Table.ReplaceValues() has a lot of interesting features, including that one.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Thanks, a sample is below. What the original column would be in my data set and then what I would like the column to look like after the transformation.
Original | Transformed |
2.399 | Rebate |
No Rebate | |
15 | Rebate |
50.1 | Rebate |
No Rebate | |
No Rebate | |
0.99 | Rebate |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |