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 !

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

Frequent Visitor

2 years ago

Solved

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

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 !

Need Help

ronrsnfld

2 years ago

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

6 Replies

ronrsnfld
Super User
2 years ago
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
  Frequent Visitor
  2 years ago
  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
    Super User
    2 years ago
    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

Forum Discussion

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

6 Replies

Related content

Table Replace Value multiple condition

Multiple conditions Index

Multiple Donut charts with conditions

Conditional Find/Replace for Multiple Values

Conditional Column with Multiple Conditions