Forum Discussion
Conditionally Replacing Cell Values
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.
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.hey
Great soltuion Mariusz. Then only thing to mension is that this step
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","1",null,Replacer.ReplaceValue,{"Custom"}),might me changed into somehting more dynamically depending on the real example of timoetzold .
have a nice day
Jimmy
