Forum Discussion
Conditionally Replacing Cell Values
Hi I must be missing something in all the posts about Replacing Values.
I have the below table (it is of course bigger) and I need to replace the Values that only have a "1" in Colum [Custom] with the correct value from Column [Custom]. Correct value is determined by the condition is not only "1" in Column [Custom] and has the same value in Column [Name].
I managed to determine the fields with the "1" in Column [Custom] and replace them with "*" with this:
= Table.ReplaceValue(#"Reordered Columns",each [Custom], each if (not Text.StartsWith([Custom], "*")) then "*" else [Custom],Replacer.ReplaceValue,{"Custom"})
I now need the 2 conditions instead of the "*" so that the result of the first replacement would be "*Your location / industry: ABC" and I just cant get it to work. Can you help?
Thanks a lot.
Name Custom
| Combined-Metric-Table-_201911292029.xlsx | 1 |
| Combined-Metric-Table-_201911292029.xlsx | *Your location / industry: ABC |
| Combined-Metric-Table-_201911292030 (1).xlsx | 1 |
| Combined-Metric-Table-_201911292030 (1).xlsx | *Your location / industry: DEF |
| Combined-Metric-Table-_201911292030.xlsx | 1 |
| Combined-Metric-Table-_201911292030.xlsx | *Your location / industry: GHI |
| Combined-Metric-Table-_201911292031.xlsx | 1 |
| Combined-Metric-Table-_201911292031.xlsx | *Your location / industry: JKL |
| Combined-Metric-Table-_201911292031.xlsx | 1 |
| Combined-Metric-Table-_201911292032.xlsx | 1 |
| Combined-Metric-Table-_201911292032.xlsx | *Your location / industry: MNO |
| Combined-Metric-Table-_201911292032.xlsx | 1 |
Hi timoetzold
Try applying this steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PTcrMS03R9U0tKcpM1g1JTMpJ1Y03MjC0NDQ0sjQyMLLUq8gprlDSUTJUitUhSYNWZH5pkUJOfnJiSWZ+noK+QmZeSmlxSVGllYKjkzNxphkbKGgYapLoBFRNYGccWoDdIS6ubsSaSbIjiHKAu4cnkeYZkuoAQ6Ic4OXtQysHGJGtAa+Lff38yXBALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Custom = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Custom", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","1",null,Replacer.ReplaceValue,{"Custom"}), #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Name", Order.Ascending}, {"Custom", Order.Ascending}}), #"Filled Up" = Table.FillUp(#"Sorted Rows",{"Custom"}) in #"Filled Up"Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
6 Replies
Hello
sorry, I don't get really what you want to achieve. Could you please post the original table and what is your expected result. Also describing you conditions as exactly as possible.
Jimmy
Hi Jimmy801 ,
thanks for the reply. Below I will post the table in its result form. That should clearify what I want to accomplish.
Essentially: If a cell value of two Cells in Column "Name" is the same it needs to also be the same in Column "Custom" and it needs to be the value of the Cell with the actual Data and not with the Dummy Filling beeing "1" in this case.
It seems easy but maybe I am thinking about it the wrong way.
This is the original.
Name Custom
Combined-Metric-Table-_201911292029.xlsx 1 Combined-Metric-Table-_201911292029.xlsx *Your location / industry: ABC Combined-Metric-Table-_201911292030 (1).xlsx 1 Combined-Metric-Table-_201911292030 (1).xlsx *Your location / industry: DEF Combined-Metric-Table-_201911292030.xlsx 1 Combined-Metric-Table-_201911292030.xlsx *Your location / industry: GHI Combined-Metric-Table-_201911292031.xlsx 1 Combined-Metric-Table-_201911292031.xlsx *Your location / industry: JKL Combined-Metric-Table-_201911292031.xlsx 1 Combined-Metric-Table-_201911292032.xlsx 1 Combined-Metric-Table-_201911292032.xlsx *Your location / industry: MNO Combined-Metric-Table-_201911292032.xlsx 1 And I needs it to look like this:
Name Custom
Combined-Metric-Table-_201911292029.xlsx *Your location / industry: ABC Combined-Metric-Table-_201911292029.xlsx *Your location / industry: ABC Combined-Metric-Table-_201911292030 (1).xlsx *Your location / industry: DEF Combined-Metric-Table-_201911292030 (1).xlsx *Your location / industry: DEF Combined-Metric-Table-_201911292030.xlsx *Your location / industry: GHI Combined-Metric-Table-_201911292030.xlsx *Your location / industry: GHI Combined-Metric-Table-_201911292031.xlsx *Your location / industry: JKL Combined-Metric-Table-_201911292031.xlsx *Your location / industry: JKL Combined-Metric-Table-_201911292031.xlsx *Your location / industry: JKL Combined-Metric-Table-_201911292032.xlsx *Your location / industry: MNO Combined-Metric-Table-_201911292032.xlsx *Your location / industry: MNO Combined-Metric-Table-_201911292032.xlsx *Your location / industry: MNO Hi timoetzold
Try applying this steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PTcrMS03R9U0tKcpM1g1JTMpJ1Y03MjC0NDQ0sjQyMLLUq8gprlDSUTJUitUhSYNWZH5pkUJOfnJiSWZ+noK+QmZeSmlxSVGllYKjkzNxphkbKGgYapLoBFRNYGccWoDdIS6ubsSaSbIjiHKAu4cnkeYZkuoAQ6Ic4OXtQysHGJGtAa+Lff38yXBALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Custom = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Custom", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","1",null,Replacer.ReplaceValue,{"Custom"}), #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Name", Order.Ascending}, {"Custom", Order.Ascending}}), #"Filled Up" = Table.FillUp(#"Sorted Rows",{"Custom"}) in #"Filled Up"Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
