Replace Values in Multiple Columns from Other Columns with Condition in PowerBI Power Query

[ckulkarni97]

I have combined two tables which have some columns matching. 

• Table 1 has 401 columns and 200 rows.
• Table 2 has 197 columns and 75 rows.
• The common key is column named "Company name".
• 17 Companies match between the 2 tables.
• 70 columns match between the 2 tables.
  • However, these columns are named differently in the 2 tables.
• There is a 3rd table given which has the names of the old and new column names.

Here is an example of what I want to do . 

Any help would be appreciated. 

Thanks in advance !

[ronrsnfld]

How can you tell that only Companies B and C are matching?

Will there be other columns to "copy over" other than Value 1 and Value 2.

Perhaps a more representative set of data for your two tables might make things simpler.

[ckulkarni97]

Thanks for your reply, please find this demo table for your ref.

Notice the Column names which are written differently in both tables.

Table 1 is the base table. I want the data for only the companies in Table 1.

However, If the companies are present in Table 2, I need to replace the values of Table 1 with the ones from Table 2.

Now I have managed to do this for 1 column . However I have 70 columns and I need to automate the process or create a loop for function I have written which will run for 70 times.

I joined Table 2 to Table 1 doing a Left outer join.

Here is the function I have written.

= Table.ReplaceValue(#"Expanded Table2",each [#"% of Male"], each if [#"Male pct"]=null then [#"% of Male"] else [#"Male pct"],Replacer.ReplaceValue,{"% of Male"} )

Hence If you observe, there is a column mapping between table 1 and Table 2:
% of Male => Male pct

% of Female => Female pct

likewise, 70 mappings are there and i need to write a formula for all these 70 mappings. Something like forming 2 lists .

1 list will hold the column names for Table 1 and 2nd list will hold the column names for Table 2

The loop will iteratively take these values and feed into the formula which I have written. 

A different approach is welcome. Thank you!

[ronrsnfld · Accepted solution]

This is a much better explanation of what you want to do.

You obviously need to create a "Map". In the code below I created manually as a Zip'd list of the mapping. Depending on more details, there may or may not be a more efficient method of doing this. 

The map has to represent all columns you wish to replace, whether or not the column names are different.

We use the Map, and the List.Accumulate function to perform the replacements.

Then we set the data types back to what they were before the replacements, as Table.ReplaceValues will change data type to any.

Note that at the beginning of the code, I set all the data types, except for the Company Name, to Int64.Type. This may not be appropriate for your actual data and you will need to  edit those lines to set the data types properly in order to have things work smoothly. The automatic setting in Power Query may be OK for your purposes.

let

//Read in the two tables
    Source = Excel.CurrentWorkbook(){[Name="Table1A"]}[Content],
    Table_1 = Table.TransformColumnTypes(Source,
      {{"Company", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type})),

    Source2 = Excel.CurrentWorkbook(){[Name="Table2A"]}[Content],
    Table_2 = Table.TransformColumnTypes(Source2,
        {{"Company", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source2),1), each {_, Int64.Type})),

//Mapping
    #"Table 1 Cols" = {"% of Males", "%of Females"},
    #"Table 2 Cols" = {"Mal pct", "Female pct"},
    Map = List.Zip({#"Table 1 Cols", #"Table 2 Cols" }),

//Join the tables using Left Outer
    #"Joined Tables" = Table.FuzzyNestedJoin(Table_1, "Company", Table_2,"Company","Joined", JoinKind.LeftOuter),
    
//Replace Values per the Map
    #"Replace Values" = List.Accumulate(
        Map,
        #"Joined Tables",
        (s,c)=> Table.ReplaceValue(
            s,
            each Record.Field(_,c{0}),
            each if Table.IsEmpty([Joined]) then Record.Field(_,c{0}) else Table.Column([Joined],c{1}){0},
            Replacer.ReplaceValue,
            {c{0}}
        )),

    #"Remove Joined Table" = Table.RemoveColumns(#"Replace Values",{"Joined"}),

    #"Reset Data Types" = Table.TransformColumnTypes(#"Remove Joined Table",
        List.Zip(
            {Table.Schema(Table_1)[Name], 
             List.Transform(
                 Table.Schema(Table_1)[TypeName], 
                 each Expression.Evaluate(_,#shared))}
                )
                )
in
    #"Reset Data Types"

Result

[ckulkarni97]

Oh Thank you so much for putting in the effort. Let me try it out with my original Dataset and get back if it worked .

Thanks a lot!

[ckulkarni97]

I have replicated your solution in my environment and it has worked JUST THE WAY I WANTED IT TO !! 

Thanks and god bless ! 

[ronrsnfld]

Glad to help.