Powerpivot : vlookup from Excel into the data model ?

[Lolo]

Hello,

I'm new in PowerPivot, and I try to play with CUBE functions.
I have a model with 1 table with 2 000 000 millions of lines, with basically 2 cols (just for the example)

Key Val
Key1 Val1
Key2 Val2
Key3 Val3

From excel, If I input the value 'Key1' in a cell, I just want to display 'Val2' in the cell just after (with a formula of course, like a basic VLOOKUP)

I have tried to use CUBE functions for that, without any success

• CUBERANKEDMEMBER
• CUBEVALUE
  
• CUBEMEMBER
• CUBEMEMBERPROPERTY

How would be the best way to achieve this ? Is it possible ?

I know that there is a LOOKUPVALUE DAX function in PowerPivot, but I'm quite sure we cannot use a excel cell/range value into powerpivot formulas, right ?

Thank you.

 

[Chihiro]

Not sure what you are after really. Can you upload sample workbook with just small set of data (about 50 rows)? And your expected/desired result.

Without sample, it sounds like the operation should really be done at PowerQuery level before data model is added to PowerPivot.

There, you can set up Named Range to pass parameter to M-Formula to make it dynamic.

For an example: To filter data for just the data matching specific criteria.
First, set named range with all possible unique values for criteria (lstCrit).
Then use that named range as data validation source. Lets say in Cell D1.
Name that cell (Criteria1).

Then use following line in M-Formula in PowerQuery to reference it.

Criteria1 = Excel.CurrentWorkbook(){[Name="Criteria1"]}[Content]{0}[Column1]

Then use Criteria1 as variable in query string.

Edit: Forgot [Content] argument in formula

 

[Lolo]

Thank you for your prompt answer.
find attached a sample file (for the sample, data is an excel table, in real life, it is a Oracle connection with a dataset of 2 000 000 rows)

In simple terms : my goal is only to query the datamodel like a basic select or a basic vlookup. I don't want to add the result to power pivot. I want to Query the datamodel

I knew that power query is able to use an excel cell in a M script, but don't manage how it could be use for my need (and I would figure out this complex, but why not)

Thank you

 

[Chihiro]

Here, see attached.

Complete M will be as below.

let
    fKey = Excel.CurrentWorkbook(){[Name="Criteria1"]}[Content]{0}[Column1],
    Source = Excel.CurrentWorkbook(){[Name="T_DATA"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([KEY] = fKey))
in
    #"Filtered Rows"

fKey is used as variable to store selected value in Cell A2. Which is named range "Criteria1". Then used in #"Filtered Rows" argument.

Select dropdown in A2 and refresh the table.

FYI - With this set up, you need to select single value for query. It cannot be empty. Also note, that currently, native library for M formula does not support wildcard filter (i.e. sql LIKE filter type of operation will need somewhat complex M code construction).

 

[Lolo]

Thank you.

However, if tehcnically it is a valid possibility to use power query like you have done, this is not really what I want:

* Each time I select a value in the list, I need to refresh the table (I suppose that by VBA, I could update automatically the table, when Criteria 1 is change, so not really an issue)

* What about If there are several columns (Val1, Val2,etc...) and I want just one ? I suppose that with M this could be managed, so probably not really an issue.

* In my real workbook, I could have 1 000 Key to request (a list of keys actually), not only 1 value, like in your lisbtox. (see new file attached)

* If I share the workbook, with other users, they would need Power query, but they haven't (while powerpivot yes)

* But globally, it is too complex regarding the "easy" need.

I finally found the solution by:
* Adding in powerpivot a measures (Use function FIRSTNONBLANK)
* Use the function CUBEVALUE to get the right info

Thank you for your help, and the way to do it by POWER QUERY is interesting anyway

 

[rrocker1405]

Hello,

I'm new in PowerPivot, and I try to play with CUBE functions.
I have a model with 1 table with 2 000 000 millions of lines, with basically 2 cols (just for the example)

Key Val
Key1 Val1
Key2 Val2
Key3 Val3

From excel, If I input the value 'Key1' in a cell, I just want to display 'Val2' in the cell just after (with a formula of course, like a basic VLOOKUP)

I have tried to use CUBE functions for that, without any success

• CUBERANKEDMEMBER
• CUBEVALUE
  
• CUBEMEMBER
• CUBEMEMBERPROPERTY

How would be the best way to achieve this ? Is it possible ?

I know that there is a LOOKUPVALUE DAX function in PowerPivot, but I'm quite sure we cannot use a excel cell/range value into powerpivot formulas, right ?

Thank you.

Once the data model is established and relationships are created amongst data model, you can use the function RELATED in Power Pivot which pretty much works like Index and Match / Vlookup. You can also lookupvalue to lookup information.

Kind regards,
A!

 

[aaronismlb1]

Thank you.

However, if tehcnically it is a valid possibility to use power query like you have done, this is not really what I want:

* Each time I select a value in the list, I need to refresh the table (I suppose that by VBA, I could update automatically the table, when Criteria 1 is change, so not really an issue)

* What about If there are several columns (Val1, Val2,etc...) and I want just one ? I suppose that with M this could be managed, so probably not really an issue.

* In my real workbook, I could have 1 000 Key to request (a list of keys actually), not only 1 value, like in your lisbtox. (see new file attached)

* If I share the workbook, with other users, they would need Power query, but they haven't (while powerpivot yes)

* But globally, it is too complex regarding the "easy" need.

I finally found the solution by:
* Adding in powerpivot a measures (Use function FIRSTNONBLANK)
* Use the function CUBEVALUE to get the right info

Thank you for your help, and the way to do it by POWER QUERY is interesting anyway

All,

I am new to this thread.

please let me know if there is a simple solution to accomplish the ability to simply reference a cell and output something based on it, using cube functions. As stated above by others I must be able to do this in the worksheet or I am going to punt and use xlookup which seems absurd. The data model I am using is constructed properly and works fine for building static financial reports using dynamic cube functions however if I have to add departments or account numbers the wheels fall off from the automation and control checking. Please help.

Also if the measure you used worked please post it as it is so that i may reverse engineeri it!

 

[vletm]

aaronismlb1​

As You're new ... You've just read Forum Rules.
Open a new thread as written there.
Reread How to get the Best Results at Chandoo.org

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org