Forum Discussion

efilipe's avatar
efilipe
Helper IV
9 years ago
Solved

Get value from another table

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!!! :-)

Need Help
Tutorial Requests
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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.

     

     

     

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      efilipe
      Helper IV
      9 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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