cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

                                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!

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

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

Syndicate_Admin
Administrator
Administrator

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!

Syndicate_Admin
Administrator
Administrator

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

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Top Kudoed Authors
Users online (2,019)