Conditionally Replacing Cell Values

[timoetzold]

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

[Jimmy801]

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

[timoetzold]

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

[Mariusz · Accepted solution]

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.

[timoetzold]

Hi @Mariusz , perfect. This works very well. I was thinking about FillUp but didn't fully understand the concept, that it only fills empty cells until there is filled cell. That understanding together with the sorting does it. Thank you very much. Best, Timo

[Jimmy801]

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

[timoetzold]

Yes @Jimmy801 I actually did change my other queries so that this step is not needed any more changing everything not valid to null beforehand. Thanks for pointing that out. Saved me some time digging for errors after loading new data ;). Best, Timo