Forum Discussion

timoetzold's avatar
timoetzold
Regular Visitor
6 years ago
Solved

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 t...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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's avatar
timoetzold
Regular Visitor
6 years ago

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.xlsx1
Combined-Metric-Table-_201911292029.xlsx*Your location / industry: ABC
Combined-Metric-Table-_201911292030 (1).xlsx1
Combined-Metric-Table-_201911292030 (1).xlsx*Your  location / industry: DEF
Combined-Metric-Table-_201911292030.xlsx1
Combined-Metric-Table-_201911292030.xlsx*Your  location / industry: GHI
Combined-Metric-Table-_201911292031.xlsx1
Combined-Metric-Table-_201911292031.xlsx*Your  location / industry: JKL
Combined-Metric-Table-_201911292031.xlsx1
Combined-Metric-Table-_201911292032.xlsx1
Combined-Metric-Table-_201911292032.xlsx*Your  location / industry: MNO
Combined-Metric-Table-_201911292032.xlsx1

 

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's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

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.

 

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      timoetzold
      Regular Visitor
      6 years ago
      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
  • timoetzold's avatar
    timoetzold
    Regular Visitor
    6 years ago
    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