Forum Discussion

jackieremidez's avatar
jackieremidez
New Member
3 years ago
Solved

Fill in text strings from one column by using data from another, and then doing it vice-versa

Hello,   I have some data that looks like this:   Prod Code A        Prod Code B       Prod Code Description 1234Q                      ABCD                            Blah                     ...
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi all,

     

    this is another option to resolve it:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNglU0lFydHJ2AVJOOYkZSrE60UpYhGBKsYmhqzU1M7cA8l3d3D1AOpCEgCgyMSURZglUBVwIVSNEOBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prod Code A" = _t, #"Prod Code B" = _t, #"Prod Code Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prod Code A", type text}, {"Prod Code B", type text}, {"Prod Code Description", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each {[Prod Code A], [Prod Code B]}, null, (x, y, z)=> if y{0} = "" then y{1} else (if y{1} = "" then y{0} else x),{"Prod Code A", "Prod Code B"})
in
    #"Replaced Value"

     

     

    jackieremidez , you need to replace the reference to #"Changed Type"  in  Table.ReplaceValue(#"Changed Type"  to the name of your last step before this needs to be actioned.

     

    Cheers,

    John

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi jackieremidez ,

Please check the m code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNglU0lFydHJ2AVJOOYkZSrE60UpYhGBKsYmhqzU1M7cA8l3d3D1AOpCEgCgyMSURZglUBVwIVSNEOBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prod Code A" = _t, #"Prod Code B" = _t, #"Prod Code Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prod Code A", type text}, {"Prod Code B", type text}, {"Prod Code Description", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Prod Code A] = "" then [Prod Code B] else [Prod Code A]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Prod Code B] = "" then [Prod Code A] else [Prod Code B]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Prod Code A"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Prod Code A"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Prod Code B"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom.1", "Prod Code B"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Prod Code A", "Prod Code B", "Prod Code Description"})
in
    #"Reordered Columns"

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data