Forum Discussion
LOOKUPVALUE with Direct Query or an alternative needed
I have looked through several similiar threads and am not finding a simple solution for this.
I am using 2 direct query sql soruces and I cannot change to import mode because I need the data to update daily. I am trying to pull values in from one table to another table. LOOKUPVALUE is not an option apparently or at least I cannot find a way to make it work. Here is a simplifed example of the 2 tables and the result I am looking to achieve.
Table 1
CONTROL_NO | CUST |
1 | FOX |
1 | FOX |
2 | PIG |
2 | PIG |
2 | PIG |
3 | DOG |
3 | DOG |
4 | SHEEP |
4 | SHEEP |
5 | COW |
6 | BEAR |
Table 2
CONTROL_NO | BULK |
1 | TRUE |
2 | FALSE |
3 | TRUE |
4 | FALSE |
5 | TRUE |
6 | FALSE |
Table 1 result I am looking for
CONTROL_NO | CUST | BULK |
1 | FOX | TRUE |
1 | FOX | TRUE |
2 | PIG | FALSE |
2 | PIG | FALSE |
2 | PIG | FALSE |
3 | DOG | TRUE |
3 | DOG | TRUE |
4 | SHEEP | FALSE |
4 | SHEEP | FALSE |
5 | COW | TRUE |
6 | BEAR | FALSE |
As I am sure you can tell, I am very new to Power BI. Any help would be truly appreciated. Thanks a lot.
Glad to help. Can you mark one/more of these as the solution so this thread can be closed.
On a semi-related matter, you don't need LOOKUPVALUE here either. You are thinking VLOOKUP in Excel. In Power BI, you should usually think merge. LOOKUPVALUE has its uses, but it is pretty rare - much rarer than doing any kind of VLOOKUP/XLOOKUP in Excel because of how merges and the data model works.See this file. It shows you how to merge those two tables and bring the TRUE/FALSE column into the first table. In Power BI, it the Transform button on the Home ribbon to get into Power Query to see it.
13 Replies
My tables did not have enough spacing but i understand what you meant. I have edited.
BULK = LOOKUPVALUE(TABLE2[BULK],TABLE2[CONTROL_NO],TABLE1[CONTROL_NO])
However, I am using direct queries, not imported data, so LOOKUPVALUE does not work and RELATED does not either. I have used the above formula in imported databases and no problem. But for direct query, I need a solution to add a column that pulls in value from another direct query based on a related field.
hi i am using direct query too and i need to recreate "user_name =LOOKUPVALUE( users[name], users[id], activities[created_by_id])" in direct query where both the tables users and activities are direct query tables. user_name is then used in a slicer like a dropdown of all those names whos id matches the activities created_by_id .
*using direct query is mandatory, cannot make any changes in pq as its a huge dataset and applying changes crashes the tables.
Thankyou so much in advance.
Here is some more detail to make the issue clearer.
Here you can see that my sources are directquery. So I am not able to edit unless I am using transform data.
OPENDET is where I need to add the info to.
OPENHEAD is where I need to pull the info from. I need the info in the BULK column. The common link is CONTROL_NO.
Here you can see when I attempt to add a column and use LOOKUPVALUE, it is not recognized.
Is there a way for me to pull the BULK info from OPENHEAD to add a column in OPENDET in transform data view?
You shouldn't be using Direct Query gaiusgw . If your data updates daily, just use a Scheduled Refresh to import the data. You can refreh data in a Pro workspace 8 times a day, and a Premium or Premium Per User workspace 48 times a day. Direct Query is almost always overused in the perceived desire to have up to date data, but it has many drawbacks:
- Time Intelligence doesn't work
- Some DAX functions don't work
- Some visuals have limited support
- The experience for the report viewer is often slower
People new to Power BI often fall into the "Direct Query gives me up to date info" trap and quickly paint themselves into a functionality corner. I did the same thing when I started. Now, I rarely use it.
edhans thanks for the explaniation and warning. I have been at this less than 1 month and assumed like you said that directquery was the best option. I am using desktop right now. Do you know if the data refreshes each time it is opened? Or must you actually click refresh? I am not finding a setting to autorefesh when opening using desktop. Also, do you know if it possible to change the source to import or must i start from scratch? Thanks a lot.
You can convert from Direct Query to Import. You cannot go the other way though. At the bottom of your report there is a button to switch. Click it and this dialog opens warning you this is a one way street, and then let's you change to Import.
The service is where scheduled refreshes happen. The desktop app is only for development. You publish to the service, then set refreshes there on a schedule. That is where you do your analysis.
Hello, any solution for the initial problem? but staying in a "Direct Query" it's mandatory for me.
thanks
