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.
This is probably a fairly simple query, but I cannot work out how to do it.
How can I replace all values that are not null across multiple columns with the word Yes.
Thank you!
Solved! Go to Solution.
Sorry.
Then I would probably add a conditional column
IF [column] = null then NULL
ELSE [column]
This calculated column will have your cleaned up values. Then you can hide the original one.
As far as I know the "Replace value" function does not have what you need.
Yes, you can do it with the Replace Values function!
In this table, column headed "F01" has empty cells that are not "blank"
Select the column or click into a cell within it. Now right-click, and select Replace Values.
Leave the top "Value to Find" box as is. In the lower box, type "null"
click "OK"
Now we have "null" in all the formerly blank cells! yay!
@tgjones43 try this:
= Table.ReplaceValue(Source, each [Column], each if [Column] = null then null else "Yes", Replacer.ReplaceValue, {"Column"})
Fragment each [Column] in second parameter is taking current content of field in each row.
How can I chnage this formula so that it takes a product and if the product is A B or C then the name is replaced with other and if false the product remains the same @Exceloholic
When you want to work within the same column, then it is still fine to use Table.ReplaceValue. That option has its limitations - you cannot to refer to values in other columns. You only can make conditions based on values in column being replaced.
Anyways, here's the scheme:
= Table.ReplaceValue(Source, each [Product], each if List.Contains({"a","b","c"}, [Product]) then "X" else [Product], Replacer.ReplaceValue, {"Product"})
Thanks @Anonymous and @Anonymous. I thought that it wasn't possible to do this using the replace all function. I'd prefer to not have to create new columns as I have so many of them. I think there may be a way to avoid having to replace values if I use a different query in a previous step, so I will try to work that out.
Thanks for your help.
@tgjones43 Hi..
Just came accross the same scenario... I found out a way to do that...
1. Select all columns you want to transform at once.
2. Change data type to irrelevant type. say, if the column has only text, change it to whole number so that all non null values would be as "errors".
3. Now, change the data type back to original text/whole number. (in your case to replace with 'yes', change it to text)
4. Transform>Replace error>'Yes'.
Thats it... Its done.. Hope it worked. Update how it worked...
Thank you @vissvess you have a great solution. In my case I had survey results with multiple columns for one question.
I needed the nulls to be 0 and anything with text to be 1. Taking his advice I converted the text column to whole number, replaced nulls with 0, and then replaced errors with 1. Viola!
Given that M does not have a "replace if different than"
https://docs.microsoft.com/en-us/powerquery-m/replacer-replacevalue
I don't believe you have options.
Unless of course you do it before importing data
In Excel you can use the wildcard "*" to identify non null cells and replace. But this doesn't work in BI
In the query editor,select your column. Then on the Transform tab on top choose Replace values and select "null" with "yes".
Repeat for every column
Thanks, but I want to leave the "null" values as "null". It is all the other values that I want to replace with "Yes". There are thousands of different values, all of which need to be "Yes".
Sorry.
Then I would probably add a conditional column
IF [column] = null then NULL
ELSE [column]
This calculated column will have your cleaned up values. Then you can hide the original one.
As far as I know the "Replace value" function does not have what you need.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |