Forum Discussion
Help with virtual tables
Hi,
I am trying to build a virtual table and lookup a value from a column of it. I have 2 tables that have a relationshio through the "Concx" field. I want to be able to add columns in the second table with the related PO numbers from the first table, in the form of "PO1 Rank1", "PO1 Rank2", etc
I can't get to a decent way of doing this.
As an example for Rank1 I built a virtual table:
VAR FilterNthProduct = FILTER ( Table1,Table1[Rank] = 1 )
However I don't know from here how to retrieve the PO2-related PO1 number.
This is what I would like to obtain:
Thanks a lot in advance for your help!
Kind regards
Valeria
Hi ValeriaBreve ,
I would prefer that you use power query to achieve this result.
Copy the po1 table and pivot the Rank field of this table,like this screenshot
result:
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
HI:
For a new table you can try = SUMMARIZE('Table 2',
'Table 2'[PO2], 'Table 2'[conc2], 'Table1'[PO2], etc ) just add table name and column for other columns. SUMMARIZECOLUMNS is good as well and you don't have to list a table name to begin like in SUMMARIZE. Just Table[Columns].This could be a start.
OK I found the way. See Solved: Filtering a lookup column - Microsoft Power BI Community
You can actually filter directly in the Lookup value function which I was not aware about, no need to for a virtual table inbetween...
Hi ValeriaBreve ,
I would prefer that you use power query to achieve this result.
Copy the po1 table and pivot the Rank field of this table,like this screenshot
result:
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, thanks a lot! This is a really neat way of doing it. However, unfortunately in my case it is not so efficient as I am doing all transformations in DAX, so these tables do not exist in PowerQuery. But I will definitely remember this for the time where I have everything in the query editor 🙂
Hi, thanks, however the idea is not to create another table.
I need to filter the existing Table1 for the rank I need and add a column to the existing Table2 per rank. This is why I would like to use a virtual table, not to multiply Table 2 filtered for each rank.However, once I filter Table2 creating a virtual table, I need to lookup the PO and to do that I need column names which I don't know how to get from virtual table....
