Big like from me. Already been utilizing the conditional replace values from your previous video and have updated my current reports to remove additional steps I had.
Fantastic Erik. If you liked this, make sure to check out my ultimate guide to replacing values. It contains some methods that I haven't recorded on RUclips yet which you may like! ^Rick gorilla.bi/power-query/replace-values/
Hi Rick, Thanks for your super educational videos on Power Query. I learned to conditionally replace values and put them in a custom column but I have a dataset and some condition like below. Condition: I have a column which *Contains* some text like "AIM", "SEO", "SELF SUPPLY" etc. and I want to create a new "Custom Column" based on this comdition and output the text as per my requirement. I want to do this within the power query. Request you to please provide your expertise and valuable inputs. Thanks.
Hello...Thank you for your video. I have a BIG PROBLEM. I want to input A's into several columns on the same row which has number 16. How do i do that please?
Is there a way to use one condition to make different replacements in the other columns? If col A value is true, then change value in column D to yes and column E to 1 for example
Hi this is great! I have multiple columns that all should have the same rules I.e yes =1 n/a = 1.5 no =0. My problem is when following the steps I only see it on 2 conditions how do I add a third?
Hi Rick, I run a test explained at 2:50 and it is working for me. I used "Table.ReplaceValue(#"Changed Type",[Overall_Rating], each if [Overall_Rating] < 5 then 5 else [Overall_Rating], Replacer.ReplaceValue,{"Overall_Rating","Service_Rating"})" and values were changed in both columns. Small update: It works ONLY if "Overall_Rating" and "Service_Rating" are with the same value. I have no clue why... 🤫🤔🤐
Hello, I have one table in three columns. 1. Accepted pages 2. Non accepted pages. 3. Total pages While accepted pages are 0 and non accepted pages are 0 then replace accepted pages 0 with total pages count. I want to work in power query. Can you please help me?
The same result can be achieved in one step by defining a custom replacer function (the 4th argument): = Table.ReplaceValue(#"Changed Type", null, null, (currentValue, oldValue, newValue) => if currentValue < 5 then 5 else currentValue, { "Overall_Rating", "Food_Rating" })
Hi Rick, thanks for your nice videos. I managed to change the value in several columns without pivoting the table by writing "Table.ReplaceValue(#"Changed Type", true, each if [Overall_Rating] < 5 then 5 else true, Replacer.ReplaceValue,{"Overall_Rating", "Food_Rating", "Service_Rating"})" . Maybe you can check if this is also working for your example :)
Not working for me neither. This is how it looks the multiple columns attempt for me: = Table.ReplaceValue(AddBOMline_ids_Column, true, each if Text.Contains([Ingrediente], "Canister") then "" else [ID externo],Replacer.ReplaceText,{"ID externo","Ref Taxonomia","ID taxonomia"}) Though, following line works for single column: = Table.ReplaceValue(AddBOMline_ids_Column, each [ID externo], each if Text.Contains([Ingrediente], "Canister") then "" else [ID externo], Replacer.ReplaceText,{"ID externo"})
Thank you very much. Excellent explanation and video. I'm stuck with a data set (from pdf) that has n tables n columns. I'm looking for text values that could be in any column. When using pivot, I'm getting "There were too many elements in the enumeration to complete the operation ". So ended up using the traditional Replace but instead of hardcoding Column names in replacer, took them from a previous step with Table.ColumnNames. Been trying to use Table.FindText function but haven't been able to nest it with a replace. Any ideas? Thanks a mil.
Hi. I think you could make the unpivot and pivot work like in the video. Could you elaborate on where you get a problem? Have a close look at how the video does it, I don't see a reason why it can't work.
If you use unpivot, replace, and then pivot, I think It's returning in error because there's duplicate value in the "value" column after you replace them. You can try to add an index column before unpivoting
Hi i have a table that has sales, and in that table I have two columns that tells you what sales is this and where it belongs,e.g I have a product columns were I have different products,then I have category that has on 3 cotagory, but now I want one category not to include one of the products, how do I update that, I can do it in the backend but I don't want to change that in the backend I want to change it in the front end
Hi, I know this isn't using Table.Replace, but to work on multiple columns you can use Accumulate; ColumnNames = Table.ColumnNames(Source) List.Accumulate( ColumnNames, Source, (S, C)=> Table.TransformColumns( S, { C , each if Value.Type (_) = type number then "number" else _ } ) ) I've tried a similar construction using Table.Replace , but have not got it to work.
I couldnt do it with the function table .replace the best I could do that seems to work is: let Source = Table.FromRows( { {1,2,2,2}, {2,5,5,5}, {3,2,5,10}, {4,6,6,6}, {5,2,5,2} }, type table [ID=Int64.Type,Overall=Int64.Type,Food=Int64.Type,Service=Int64.Type]), #"Cleaned Text" = Table.TransformColumns( Source, { {"Overall", each Replacer.ReplaceValue(_,_,if _
Okay my friend. I watched your video and the idea of pivoting a table for this made me angry so here is what I found after a lot of fiddling around. It's basically the extended version of the (x,y,z)=> if y then z else x which you mention in your blog post Looking at the Replace.ReplaceValue function you see it takes three arguments value, old and new. And it's a dumb little function that amounts to (value as any, old as any, new as any) as any => if value = old then new else old value is of course the cell value, old and new are passed through from Table.ReplaceValue. Using an inline function we can just substitue the Replacer.ReplaceValue function and do Table.ReplaceValue(#"Changed Type", null, null, (value as any, old as any, new as any) as any=> if value < 5 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"}) Now that we are controlling the function and know what exactly is being passed and how, we can do whatever. For example we could pass the Record for the current row in new or old and only adjust the ratings for a specific restaurant doing this: Table.ReplaceValue(#"Changed Type", each _, null, (value as any, old as any, new as any) as any=> if value < 5 and old[Restaurant_ID] = 132663 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"}) Of course for that we don't have to pass the entire record because we can put the boolean result of that comparison right into one one of the parameters like this Table.ReplaceValue(#"Changed Type", each [Restaurant_ID] = 132663 , null, (value as any, old as any, new as any) as any=> if value < 5 and old = true then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"}) or maybe we figured out that U1021 is the manager of 132663 and is leaving negative ratings for competing locations to make himself look better in compariso, so we give all negative reviews he is leaving to his own restaurant and assign them to a different user to cover our tracks Table.ReplaceValue(#"Changed Type", each _, null, (value as any, old as any, new as any) as any=> if old[Overall_Rating] < 5 and old[Consumer_ID] = "U1021" then if value ="U1021" then "U1337" else 132663 else value,{"Consumer_ID","Restaurant_ID"})
I read his blog too and saw in his example which applied custom function, the Oldvalue parameter is a condition that reference from other column. But in this video's example, the condition comes from their own => Im not sure but I think It a different So, I apply your method, It works for me, thanks a lot Table.ReplaceValue(#"Changed Type", null, null, (value as any, old as any, new as any) as any=> if value < 5 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"})
Rick, All your tutorials, and even the specific topics you choose to focus on are all high-class Masterclasses! Thank you!
Very clever workaround.
Also love that "Replace Value Type" trick. Thanks Rick. :D
Big like from me.
Already been utilizing the conditional replace values from your previous video and have updated my current reports to remove additional steps I had.
Fantastic Erik. If you liked this, make sure to check out my ultimate guide to replacing values. It contains some methods that I haven't recorded on RUclips yet which you may like!
^Rick
gorilla.bi/power-query/replace-values/
I love all these techniques you show - very clever. Love your videos.
Impressive ability to simplify and teach
thx for your enjoy, the useage of value.replacetype and valu.typy is so great
worked a treat and saved me from creating new columns, thanks
Great Video! That worked like a charm! Thanks for taking the time to put this together.
Thx man :) I tride to fix my problem and when a watched yours film everything was simple :)
Very inspirational! Learned a lot from you
Hi Rick, Thanks for your super educational videos on Power Query. I learned to conditionally replace values and put them in a custom column but I have a dataset and some condition like below.
Condition:
I have a column which *Contains* some text like "AIM", "SEO", "SELF SUPPLY" etc. and I want to create a new "Custom Column" based on this comdition and output the text as per my requirement. I want to do this within the power query.
Request you to please provide your expertise and valuable inputs. Thanks.
Very helpful. Thank you very much
Extremely useful! Thanks :-)
🔥 glad it helps!
Hello...Thank you for your video. I have a BIG PROBLEM. I want to input A's into several columns on the same row which has number 16. How do i do that please?
Thank you a lot
Can this method work if you have to transform all of the columns ?
Is there a way to use one condition to make different replacements in the other columns?
If col A value is true, then change value in column D to yes and column E to 1 for example
Thanks for sharing 🤓
Ofcourse! And more is coming. Next week about replacing case insensitive 😎😎
I like it! Thank you!
Thanks Mariusz! 🙌
Hi this is great! I have multiple columns that all should have the same rules I.e yes =1 n/a = 1.5 no =0. My problem is when following the steps I only see it on 2 conditions how do I add a third?
Hi Rick, I run a test explained at 2:50 and it is working for me. I used "Table.ReplaceValue(#"Changed Type",[Overall_Rating], each if [Overall_Rating] < 5 then 5 else [Overall_Rating], Replacer.ReplaceValue,{"Overall_Rating","Service_Rating"})" and values were changed in both columns.
Small update: It works ONLY if "Overall_Rating" and "Service_Rating" are with the same value. I have no clue why... 🤫🤔🤐
Hello,
I have one table in three columns. 1. Accepted pages 2. Non accepted pages. 3. Total pages
While accepted pages are 0 and non accepted pages are 0 then replace accepted pages 0 with total pages count.
I want to work in power query.
Can you please help me?
this was really helpfull
The same result can be achieved in one step by defining a custom replacer function (the 4th argument):
= Table.ReplaceValue(#"Changed Type",
null,
null,
(currentValue, oldValue, newValue) =>
if currentValue < 5 then 5 else currentValue,
{ "Overall_Rating", "Food_Rating" })
Nice video!
Thank you !! appreciate the support Alfonso 🙌
Hi Rick, thanks for your nice videos. I managed to change the value in several columns without pivoting the table by writing "Table.ReplaceValue(#"Changed Type", true, each if [Overall_Rating] < 5 then 5 else true, Replacer.ReplaceValue,{"Overall_Rating", "Food_Rating", "Service_Rating"})" . Maybe you can check if this is also working for your example :)
It doesn't work for me 🤔
Not working for me neither. This is how it looks the multiple columns attempt for me:
= Table.ReplaceValue(AddBOMline_ids_Column, true, each if Text.Contains([Ingrediente], "Canister") then "" else [ID externo],Replacer.ReplaceText,{"ID externo","Ref Taxonomia","ID taxonomia"})
Though, following line works for single column:
= Table.ReplaceValue(AddBOMline_ids_Column, each [ID externo], each if Text.Contains([Ingrediente], "Canister") then "" else [ID externo], Replacer.ReplaceText,{"ID externo"})
Very useful, thanks, I subscribed ;)
Thank you very much. Excellent explanation and video. I'm stuck with a data set (from pdf) that has n tables n columns. I'm looking for text values that could be in any column. When using pivot, I'm getting "There were too many elements in the enumeration to complete the operation ". So ended up using the traditional Replace but instead of hardcoding Column names in replacer, took them from a previous step with Table.ColumnNames. Been trying to use Table.FindText function but haven't been able to nest it with a replace. Any ideas? Thanks a mil.
Hi. I think you could make the unpivot and pivot work like in the video. Could you elaborate on where you get a problem?
Have a close look at how the video does it, I don't see a reason why it can't work.
If you use unpivot, replace, and then pivot, I think It's returning in error because there's duplicate value in the "value" column after you replace them.
You can try to add an index column before unpivoting
can u helpe me, i need to replace punctuation on my dataset.
Hi i have a table that has sales, and in that table I have two columns that tells you what sales is this and where it belongs,e.g I have a product columns were I have different products,then I have category that has on 3 cotagory, but now I want one category not to include one of the products, how do I update that, I can do it in the backend but I don't want to change that in the backend I want to change it in the front end
So how do I use the power query if statements to do that
So how do I use the power query if statements to do that
Hi, I know this isn't using Table.Replace, but to work on multiple columns you can use Accumulate;
ColumnNames = Table.ColumnNames(Source)
List.Accumulate( ColumnNames, Source, (S, C)=>
Table.TransformColumns( S,
{ C , each if Value.Type (_) = type number then "number" else _ } ) )
I've tried a similar construction using Table.Replace , but have not got it to work.
Thanks William. That’s a great template. If you want to use List.Accumulate with Table.ReplaceValue you could use:
= List.Accumulate(
{ {"A","Ab"}, {"B", "Bc"}, {"C", "Cd"}, {"D","De"} },
[
ReplaceThis = null
,ReplaceFor = null
,MyTable = Source
] ,
( state, current ) =>
[
ReplaceThis = current{0}
,ReplaceFor = current{1}
,MyTable = Table.ReplaceValue(
state[MyTable],
current{0},
current{1},
Replacer.ReplaceText,{"Value"}
)
]
)[MyTable]
@@BIGorilla Thank you for all your videos, I've learnt so much.
I have done it in 12 lines of scripts for a whole year.
How to filldown firstnonblank values in multiple columns based on condition.
Hey bayapa, Can you elaborate with example data and a desired outcome?
I couldnt do it with the function table .replace the best I could do that seems to work is:
let
Source =
Table.FromRows(
{
{1,2,2,2},
{2,5,5,5},
{3,2,5,10},
{4,6,6,6},
{5,2,5,2}
},
type table [ID=Int64.Type,Overall=Int64.Type,Food=Int64.Type,Service=Int64.Type]),
#"Cleaned Text" =
Table.TransformColumns(
Source,
{
{"Overall", each Replacer.ReplaceValue(_,_,if _
Okay my friend. I watched your video and the idea of pivoting a table for this made me angry so here is what I found after a lot of fiddling around. It's basically the extended version of the
(x,y,z)=> if y then z else x
which you mention in your blog post
Looking at the Replace.ReplaceValue function you see it takes three arguments value, old and new.
And it's a dumb little function that amounts to
(value as any, old as any, new as any) as any => if value = old then new else old
value is of course the cell value, old and new are passed through from Table.ReplaceValue. Using an inline function we can just substitue the Replacer.ReplaceValue function and do
Table.ReplaceValue(#"Changed Type", null, null, (value as any, old as any, new as any) as any=> if value < 5 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"})
Now that we are controlling the function and know what exactly is being passed and how, we can do whatever.
For example we could pass the Record for the current row in new or old and only adjust the ratings for a specific restaurant doing this:
Table.ReplaceValue(#"Changed Type", each _, null, (value as any, old as any, new as any) as any=> if value < 5 and old[Restaurant_ID] = 132663 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"})
Of course for that we don't have to pass the entire record because we can put the boolean result of that comparison right into one one of the parameters like this
Table.ReplaceValue(#"Changed Type", each [Restaurant_ID] = 132663 , null, (value as any, old as any, new as any) as any=> if value < 5 and old = true then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"})
or maybe we figured out that U1021 is the manager of 132663 and is leaving negative ratings for competing locations to make himself look better in compariso, so we give all negative reviews he is leaving to his own restaurant and assign them to a different user to cover our tracks
Table.ReplaceValue(#"Changed Type", each _, null, (value as any, old as any, new as any) as any=> if old[Overall_Rating] < 5 and old[Consumer_ID] = "U1021" then if value ="U1021" then "U1337" else 132663 else value,{"Consumer_ID","Restaurant_ID"})
I read his blog too and saw in his example which applied custom function, the Oldvalue parameter is a condition that reference from other column. But in this video's example, the condition comes from their own => Im not sure but I think It a different
So, I apply your method, It works for me, thanks a lot
Table.ReplaceValue(#"Changed Type", null, null, (value as any, old as any, new as any) as any=> if value < 5 then 5 else value,{"Overall_Rating", "Food_Rating", "Service_Rating"})