Replace column data of one data table with column data from another table based on a condition

Question

I have 2 data tables connected in my powerbi workbook. My objective is to replace a column data with a column data from another table in powerbi.

Below is the sample example.

Table 1:

Customer	Action 1	Action2	ID
A	Open	InProg	1001
B	Closed	Sent	1002
B	On Hold	Waiting	1003

Table 2:

Customer	Action 1	Action2	ID
B	Open	InProg	1002
B	Closed	Sent	1003
B	On Hold	Waiting	1004

Expected Output (Table 1 rows should replace with Table 2 matching rows based on Customer and ID columns):

Customer	Action 1	Action2	Action3
A	Open	InProg	1001
B	Open	InProg	1002
B	Closed	Sent	1003

Given a line (Customer, Action1, Action2, ID) from Table1, are all the field (Customer, Action1, Action2) ignored if ID is found in Table2 ? — Frédéric LOYER, Apr 16 '23 at 16:41
I need Table 2 data (Action1 and Action2) to be reflected in Table 1 when matching the customer and ID fields. And it shouldn't affect data related to other customers. — mounica dhulipudi, Apr 16 '23 at 16:53

Frédéric LOYER · Answer 1 · 2023-04-16T17:07:09.793

With the current specifications, I give you the following code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypesModified = Table.TransformColumnTypes(Source,
       {{"Customer", type text}, {"Action 1", type text},
        {"Action2", type text}, {"ID", Int64.Type}}),
    MergedQuery = Table.NestedJoin(TypesModified, {"Customer", "ID"},
       Table2, {"Customer", "Action3"}, "Table2", JoinKind.LeftOuter),
    Table2Developped = Table.ExpandTableColumn(MergedQuery, "Table2",
       {"Action 1", "Action2"}, {"Table2.Action 1", "Table2.Action2"}),
    AddAction1 = Table.AddColumn(Table2Developped, "The.Action1",
       each if [Table2.Action 1]=null then [Action 1]
                                      else [Table2.Action 1]),
    AddAction2 = Table.AddColumn(AddAction1, "The.Action2", 
       each if [Table2.Action2]=null then [Action2]
                                      else [Table2.Action2]),
    SelectedColumns = Table.SelectColumns(AddAction2,
            {"ID", "Customer", "The.Action1", "The.Action2"}),
    PermutedColumns = Table.ReorderColumns(SelectedColumns,
            {"Customer", "The.Action1", "The.Action2", "ID"}),
    RenamedColumns = Table.RenameColumns(PermutedColumns,
            { {"The.Action1", "Action1"}, {"The.Action2", "Action2"}}),
    Sorted = Table.Sort(RenamedColumns,{{"ID", Order.Ascending}})
in
    Sorted

Note, this has been tested in Excel (see the Source= line). The script is picky about column name and works with your tables. Beware, you have Action 1 with a space and Action2, no space. This script needs a Table2defined.

Note that the table expansion, after the Merge, doesn't preserve the order of the line, then the last action is a sort on ID.

Here, the idea is to get Action1 and Action2 from Table2 when available; and fallback to Table1 if not. This decision is on AddAction1= and AddAction2= lines.

score 0 · Answer 2 · answered Apr 17 '23 at 08:18

create [index] column in both table 1&2:

Index = [Customer]&[ID]
create "table 3" with formula:

Table 3 = SUMMARIZE('Table 1',[Customer],[ID],[Index])
set relationships between table 1 & table 3, table 2 & table 3 respectively with [index] column:
create columns with formula:

Action 1 = if(related('Table 2'[Action 1])=blank(),related('Table 1'[Action 1]),(related('Table 2'[Action 1])))

Action 2 = if(related('Table 2'[Action2])=blank(),related('Table 1'[Action2]),(related('Table 2'[Action2])))

Replace column data of one data table with column data from another table based on a condition

2 Answers2