Need help on how to add values from first table and replace with current value in second table

[Mahi1827]

I have scenario like below.
I have two tables results as below

Table 1
--------
Channel customer  viewcount
A1           C1            2500

Table 2
--------
Channel customer viewcount
B1          C2            4500
B1          C3            5500

Here i need to summarize two tables and replacing A1 with B1, here Table 2 results always same and need to add exsiting table 1 results to table 2 by replacing by table 2 channel.

expected output like below

Channel  Customer   Viewcount
B1            C1             2500 ( this record from channel A1 from Table 1)
B1            C2             4500
B1            C3             5500

I have tried with Append and Union but dynamically i am unable to find the solution.

Please look into this and suggest me with DAX approach.

Thank you for your time and help in advance.

Thanks,
Mahi18

[Anonymous · Accepted solution]

Hi @Mahi1827 ,

You could try to write a DAX expression to create a new table.

NewTable = 
VAR t1 =
    SELECTCOLUMNS (
        'Table 1',
        "Channel", SUBSTITUTE ( 'Table 1'[Channel], "A1", "B1" ),
        "customer", 'Table 1'[customer],
        "viewcount", 'Table 1'[viewcount]
    )
RETURN
    UNION ( t1, 'Table 2' )

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Mahi1827 , Use replace in transform data/power Query . Right-click on column there is option

https://www.howtoexcel.org/power-query/bulk-replace-values/

Then merge the two data

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Greg_Deckler]

@Mahi1827  Well, there is a SUBSTITUTE function and a REPLACE function in DAX but I am not understanding the logic here. I suppose there might be a DAX solution, but wouldn't it be easier to just right-click the column header in Power Query and Replace "A1" with "B1" ?