Get value from another table

[efilipe]

Hi guys!

I have 3 tables (that has more columns than that, of course)

1) CLIENT

CLIENT_ID

CITY_NAME

2) PROVIDER

PROVIDER_ID

CITY_ID

2) CITY

CITY_ID

CITY_NAME

For the 3 tables, I'm loading though CSV file.

The problem is the Client table doesnt have the ID, but the City name. I want to change that column to CITY_ID.

Can anyone help me on that? 🙂 Thank you!!! 🙂

[Phil_Seamark · Accepted solution]

Hi @efilipe

Have you had a play with the Merge Tables feature in the Query Editor?

It's really good and may help you produce a table that combines this data into a single table.

[Phil_Seamark · Accepted solution]

Hi @efilipe

Have you had a play with the Merge Tables feature in the Query Editor?

It's really good and may help you produce a table that combines this data into a single table.

[efilipe]

Hi Phil,

Dont know if thats the best way to go, bcause I'm giving one example, bu it happens 5 times... And this table is appended to another one after the changes... But I'll try. Thanks!

[v-shex-msft · Accepted solution]

Hi @efilipe,

You can try to use Table.Join and Table.SelectColumns to achieve your requirement:

Sample:

let
    Source1= Table.SelectColumns(CITY,{"CITY_ID","CITY_NAME"}),
    Source2= Table.SelectColumns(CLIENT,{"CLIENT_ID","CITY_NAME"}),
    Source = Table.SelectColumns(Table.Join(CLIENT,"CITY_NAME",CITY,"CITY_NAME",JoinKind.LeftOuter),{"CLIENT_ID","CITY_ID"})
in
    Source

Regards,

Xiaoxin Sheng