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

[jackieremidez]

Hello,

I have some data that looks like this:

Prod Code A        Prod Code B       Prod Code Description

1234Q                      ABCD                            Blah

                                ABCD                            Blah#

1234Q                                                            Blah

1234Q                     ABCD                            Blah

5678                       EFGH

5678                                                           Yada

                               EFGH                            Ya-da

5678                       EFGH                            Yada

I want to use the Prod Code A data to fill in the missing Prod Code B Data and vice versa. I'm not worried about how to clean up Prod Code Description; just wanted to give a flavor of the hot mess I have hahaha! I have about 15M rows of data and about 5M rows with missing info in either the first or the second code column.

Thank you!

[jbwtp · Accepted solution]

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

[jackieremidez]

Hi,

I should have mentioned that a) my skills are limited, b) the table I'm trying to use for my data is 16 appended Excel files (Jan2022_1, Jan2022_2, Feb2022_1, Feb2022_2, etc.) which I am calling Jan-Aug2022, and c) there are other columns in the file that I don't need to manipulate (yet, and this might be irrelevant). So...I'm trying to test your code below, which works beautifully and returns the sample data...but I don't know how to use my own combined table as the data source. Thank you again for your help!

[v-cgao-msft]

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