update a column value from different tables

[Jeffreyjar]

Hello everyone, 

@AlexisOlson 

please help i have an issue

i want to update the column values(Numero Agents) of my APE 02 2022 table

Numero Agents	DDS_X1_SPLIT	DDS_CA_MPREM	DDS_CA_QPREM	DDS_CA_SPREM	DDS_CA_APREM	APE per agents
A99999009	0,5	20000	57332	111997	219160	120000
A99999009	1	25000	71777	140333	274722	300000
A99999009	0,5	30000	86222	168667	330279	180000
A99999009	0,5	20100	57620	112563	220271	120600
A99999009	0,5	20200	57911	113133	221388	121200
A99999009	0,5	35000	100668	197002	385836	210000
A79999021	0,5	100000	288444	565333	1108055	600000
A79999021	0,5	100000	288445	565334	1108057	600000
A79999021	0,5	50000	144000	282000	552500	300000
A79999021	0,5	100000	288445	565334	1108057	600000
A79999021	0,5	50000	144000	282000	552501	300000
A79999021	0,5	50000	144000	282000	552501	300000
A99999009	0,5	20000	57333	111999	219165	120000

with my columns(New Agents Codes and associate their names(EEAGTNAME)) of the table *agents list*

EAAGENT	EAAGTNAME	EAADDR1	EACITY	EAFCR	EASTATUS	New Agents Codes
A99999009	MOUAFFI SINDEU BRICE CEDRICK		YAOUNDE	A99901001	A	A99901001
A79999021	ATSOL NDONGO NATHALIA		YAOUNDE	A79901001	A	A79901001

so that i could have this final data

Numero Agents	DDS_X1_SPLIT	DDS_CA_MPREM	DDS_CA_QPREM	DDS_CA_SPREM	DDS_CA_APREM	APE per agents	New Numero Agents	Names
A99999009	0,5	20000	57332	111997	219160	120000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	1	25000	71777	140333	274722	300000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	30000	86222	168667	330279	180000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	20100	57620	112563	220271	120600	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	20200	57911	113133	221388	121200	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	35000	100668	197002	385836	210000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A79999021	0,5	100000	288444	565333	1108055	600000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	100000	288445	565334	1108057	600000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	50000	144000	282000	552500	300000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	100000	288445	565334	1108057	600000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	50000	144000	282000	552501	300000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	50000	144000	282000	552501	300000	A79901001	ATSOL NDONGO NATHALIA
A99999009	0,5	20000	57333	111999	219165	120000	A79901001	ATSOL NDONGO NATHALIA

[Jeffreyjar]

It is working thanks

[PaulDBrown]

Sorry, I'm not sure what you mean. Can you expand on what else you need?

[Jeffreyjar]

this is the expected data sample that i want to look like

EEAGENT has old values. New Agents Codes are the new ones

So i want to return New Agents Codes values

Numero Agents	DDS_X1_SPLIT	DDS_CA_MPREM	DDS_CA_QPREM	DDS_CA_SPREM	DDS_CA_APREM	APE per agents	New Numero Agents	Names
A99999009	0,5	20000	57332	111997	219160	120000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	1	25000	71777	140333	274722	300000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	30000	86222	168667	330279	180000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	20100	57620	112563	220271	120600	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	20200	57911	113133	221388	121200	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A99999009	0,5	35000	100668	197002	385836	210000	A99901001	MOUAFFI SINDEU BRICE CEDRICK
A79999021	0,5	100000	288444	565333	1108055	600000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	100000	288445	565334	1108057	600000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	50000	144000	282000	552500	300000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	100000	288445	565334	1108057	600000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	50000	144000	282000	552501	300000	A79901001	ATSOL NDONGO NATHALIA
A79999021	0,5	50000	144000	282000	552501	300000	A79901001	ATSOL NDONGO NATHALIA
A99999009	0,5	20000	57333	111999	219165	120000	A79901001	ATSOL NDONGO NATHALIA

[PaulDBrown · Accepted solution]

Create a new column using:

New Numero Agent = 
LOOKUPVALUE('table *agents list*'[New Agents Codes],'table *agents list*'[EAAGENT], 'APE 02 2022 table'[Numero Agents])

[Jeffreyjar]

Thank you, the second solution worked for me,but you have done only for the names what about the codes the new ones

[Jeffreyjar]

there is already a relationship between them, but it is more complicated than you think i just need a solution for the problem requested with an example if possible

[PaulDBrown]

If there is a relationship between the two tables,

you can add a new column using:

Agent Name =
RELATED ( 'table *agents list*'[EAAGTNAME] )

If there isn't a relationship,

you can use:

Agent Name = LOOKUPVALUE('table *agents list*'[EAAGTNAME],'table *agents list*'[EAAGENT], 'APE 02 2022 table'[Numero Agents])

[rbriga]

It is a matter of a join, or "merge queries" in the Query Editor.

1. Add this 2 sources as queries.

2. For the first table, go to the query editor and click "Merge Queries"

3. Merge the 2 queries based on Numero Agents in table 1 and EAAGENT in table 2

4. extend table 1 to hold New Numero Agents from table 2

See this page, under "combine queries".

[Jeffreyjar]

I cannot do that with power query because New Agents Codes is a calculated column

Dax functions(formulas) are needed

[rbriga]

How about creating a relationship between the two tables, based on Numero Agents in table 1 and EAAGENT in table 2?

You can then use the New Agents Codes in any tabe you display as a visual.

You can create a table using one of the table functiones, but the above solution should do.