Forum Discussion
Need help on how to add values from first table and replace with current value in second table
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
Hi Anonymous ,
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.
3 Replies
- Greg_Deckler
Community Champion
Anonymous 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" ?
- amitchandak
Super User
Anonymous , 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
Hi Anonymous ,
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.
