How to recreate multiple vlookups in one column in power bi

[krfaughnan]

I have a some what complicated report scenario that I am attempting recreate in Power BI. I am new to the application so I am not entirely sure of the best way to accomplish this or if it's even possible. Basically I publish a report that reports on which version of an application a client is running. Unfortunately, there are 3 different version sources (databases) that are no integrated and do not share all of the same columns (but do all share a GUID) so I have to join them manully/separately by extracting the date from all 3 sources in an Excel file (3 total source files). I basically copy them into separate tabs and then have to represent the data the same "version" column by doing the following:

Version column steps

1. Enter a vlookup for version source #1 and copy down the Version column.
2. Filter the blanks in the version column, clear the vlookup from the blank rows, enter a vlookup for version source #2 and copy down the column
3. Filter the blanks in the version column, clear the vlookup from the blank rows, enter a vlookup for version source #3 and copy down the column.

Please understand that I already know this is messy, especially being as though I've been doing this manually, but I do not have control over this at this time - i.e., please see: this is an executive's request 🙂 . I also can not connect directly to these db's so I will still be working with excel files. Has anyone done anything like this before? I have tried looking up a solution in this fourm but can't seem to find anyone who has successfully done the same kind of thing.

[jthomson]

It's surely got to be easiest to do this in Power Query by merging your three lookup tables to your main table to get columns lookup1, lookup2 and lookup3 for each value in the main table, and then just make a calculated column saying if lookup1's not blank then lookup1 else if lookup2's not blank etc

[Anonymous]

@krfaughnan,

If you can provide me with a test data of those 3 data source, I can try to provide with the solution. Thanks.

Best,

Duc

[krfaughnan]

Thanks for the quick reply! I can't actually give you any test data because PHI is involved. But it's basically seems to be a simple if/else but I can not figure out how to do this in the application becasue it does not seem to want to allow a third "if". It's something like...

if version source #1 has version data for the client, then enter data into the Version column

else

if version source #2 has version data for the client, then enter data into the Version column

else

if version source #3 has version data for the client, then enter data into the Version column

else 

leave column blank

[v-jiascu-msft]

Hi @krfaughnan,

If you can't share the file, you can type a sample here. Because we need the data and its structure. 

Where did you try to add a third "if"? 

What's the expected result?

How to enter data into the version column?

Best Regards,
Dale

[krfaughnan]

Thanks for your response! Below is the end result  of what I am trying to accomplish with the scenario I am faced with. Basically I imported 3 excel files (Version source #1, 2 & 3) and created 3 tables from them. I have another table (Master Version table) I created (all 3 source tables are joined to this table based on a GUID) and it is in that table I am trying to bring in data based on a set of rules where data is populated from Version souce #1 if there is data in that table for that client, if not populate from Version souce #2 if there is data in that table for that client, if not populate from Version souce #3 if there is data in that table for that client, if not leave the column blank.

My source tables look like this: 

Version Source #1		Version Source #2		Version Source #3
Client	Version		Client	Version		Client	Version
Client 1	123		Client 1			Client 1
Client 2			Client 2	456		Client 2
Client 3			Client 3			Client 3	789
Client 4	123		Client 4			Client 4
Client 5			Client 5	456		Client 5
Client 6			Client 6			Client 6	789

And the end result ideally would look like this:

Master Version table
Client	Version	Version Source
Client 1	123	Version Source #1
Client 2	456	Version Source #2
Client 3	789	Version Source #3
Client 4	123	Version Source #1
Client 5	456	Version Source #2
Client 6	789	Version Source #3

[krfaughnan]

Sorry, those tables did not format as expected.

My version source tables look like this:

And this is the desired end result I am pursuing: 

[Anonymous]

@krfaughnan,

This can be done by DAX or PowerQuery, I will provide you 2 different DAX solution:

Start with your data sample:

SOLUTION 1: your end result:

In Modeling -> Create Table and use following DAX:

Summarize  = 
ADDCOLUMNS(
    UNION(VALUES(Table5[GUID]),
          VALUES(Table6[GUID]),
          VALUES(Table7[GUID])),
    "Version", VAR Version1 = CALCULATE(MAX(Table5[Version]),FILTER(Table5,Table5[GUID] = EARLIER([GUID]))) RETURN
               VAR Version2 = CALCULATE(MAX(Table6[Version]),FILTER(Table6,Table6[GUID] = EARLIER([GUID]))) RETURN
               VAR Version3 = CALCULATE(MAX(Table7[Version]),FILTER(Table7,Table7[GUID] = EARLIER([GUID]))) RETURN
               
               IF(ISBLANK(Version1),
                        IF(ISBLANK(Version2),Version3,Version2),Version1),

    "From",    VAR Version1 = CALCULATE(MAX(Table5[Version]),FILTER(Table5,Table5[GUID] = EARLIER([GUID]))) RETURN
               VAR Version2 = CALCULATE(MAX(Table6[Version]),FILTER(Table6,Table6[GUID] = EARLIER([GUID]))) RETURN
               VAR Version3 = CALCULATE(MAX(Table7[Version]),FILTER(Table7,Table7[GUID] = EARLIER([GUID]))) RETURN
               
               IF(ISBLANK(Version1),
                        IF(ISBLANK(Version2),"From Database 3","From Database 2"),"From Database 1"))

SOLUTION: another format:

In Modeling -> Create Table and use following DAX:

Summarize 2 = 
ADDCOLUMNS(
    UNION(VALUES(Table5[GUID]),
          VALUES(Table6[GUID]),
          VALUES(Table7[GUID])),
    "Version1", CALCULATE(MAX(Table5[Version]),FILTER(Table5,Table5[GUID] = EARLIER([GUID]))),
    "Version2",CALCULATE(MAX(Table6[Version]),FILTER(Table6,Table6[GUID] = EARLIER([GUID]))),
    "Version3",CALCULATE(MAX(Table7[Version]),FILTER(Table7,Table7[GUID] = EARLIER([GUID]))))

Let me know if this helps. You can also do this in Power Query with M. Thanks.

Duc

[krfaughnan]

Thank you! But this actually creates 3 seperate columns - I need one verison column.

[MarkLaf]

If I'm not misunderstanding anything, this is pretty straigthforward to do in the query editor just through the UI (i.e. no M coding).

1. Load in each Version Source Table
2. In each Version Source Table, do Add Column ribbon > Custom Column, and then name the column and set the "tag" you want to use in your master version table:
   
3. Then, go to Home ribbon, Append Queries dropdown (all the way to the right in the Combine group), select Append Queries as New:
   
4. In Append window, select the Three or more tables radio button at the top, then move all three Version Source tables into Tables to append area
   
5. Now you'll have the combined Master Version Table with the From column filled out; last step is to filter out the nulls:
   
6. And that's it (if needed you can sort the Client/GUID column, too):
   

[krfaughnan]

FYI! I just figured it out - I wound up using the switch function 🙂