VLOOKUP based off different tables

[deaddingo]

Hi All

Apologies if this type of thing has been asked numerous times, but i am struggling a bit with it.

I have numerous tables set up in a data model.   Some are being pulled from SFDC Reports while others (table Summary) is from Excel.   

As an example I have 3 different tables from SFDC (Rod, Jane and Freddy) which included the columns below.    The Table Summary (from Excel) is where I am consolidating the various reference numbers in one table.  I need this summary sheet to pull the status and comments from the relevant SFDC Table if the Reference number matches, as shown above.

If someone could help with how I can do this I would really appreciate it.  I will then try to use this info on the real data.

Many thanks

[Anonymous · Accepted solution]

Hi @deaddingo ,

As long as they have the same column names, they can be appended successfully.

If the column names are different, let me give you an example. If I renamed the Status in the ROD table to Status 1, the following effects will appear.

Then right-click the column name to sort, the results are as follows

You just select append query as new. If it is not right, just delete the newly appeared table.

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.

[deaddingo]

Thanks Stephen (and everyone for their replies).

I will give this a try.

Appreicate the help.

[ERD]

Hi @deaddingo ,

I would append tables as well. In case you have different columns structure but similar data, you need to prepare these queries for the append operation: select mutial columns, standardize column names, etc. 

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

[deaddingo]

Hi @ERD 

As these have been pulled direct from SFDC, would modifying the column names in Bi have any affect on this?  Also,  what would happen to the columns that are not the same in all the tables?

Thanks

[ERD]

@deaddingo , I was talking about preparations in Power Query.

1. If you append the tables with the same column names, then you will get your summary table:

2. You can get rid of non mutial columns using Home tab - Choose Columns. Otherwise you will get null values for non mutial columns:

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

[HotChilli]

Why don't you 'Append Queries' on the 3 tables (in Power Query) and throw away the Excel summary table?

[deaddingo]

Unfortunately the 3 tables don't all have the same columns in them.   The examples were just a very simplified version so I cant append unfortunately.

[mahoneypat]

The three tables on the left should be appended in the query editor in Power BI (or Excel), since they have the same columns.  You can disable load on them so they are not part of your model, but you can then load the new table and the one on the right and relate them, or you can first merge them in the query editor to have just one table to load.

Pat

[deaddingo]

Unfortunately the 3 tables don't all have the same columns in them.   The examples were just a very simplified version so I cant append.