cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gaiusgw
Helper II
Helper II

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. 

2 ACCEPTED SOLUTIONS

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Awesome! Thanks a lot @edhans, really appreciate the advice. 👍

View solution in original post

11 REPLIES 11
edhans
Super User
Super User

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:

  1. Time Intelligence doesn't work
  2. Some DAX functions don't work
  3. Some visuals have limited support
  4. 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. 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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.

2021-03-23_11-46-27.png

 

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.

 
 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

So i did covert to import but i see an issue. some of the tables i am working with have over 1.6 million lines and so it takes about 15 minutes to refresh on desktop currently. this is 5 years worth of data so i can expect in another 5 years it will take 30 minutes to load and so on. directquery takes less than 30 seconds to load. am i in a rock and a hard place here? either take 15-30 mintues to load everyday or use directquery and not have the certain functions like LOOKUPVALUE? is there a way to import just new lines to cut down refresh time? 

You would do this using Incremental Refresh - in the service. You set it up in the desktop, but when you publish, it will only refresh recent data - whatever that is for you. Last 5 days, 5 weeks, 2 years. Whatever. The older data is retained. 

But even if you don't use incremental refresh, the service refreshes automatically. So you said this was updated daily. So set the refresh to happen at 11pm. When you come in the next morning, it is up to date. You can even work in the report in the service while it is refreshing. When it is done, it will show you newer data if you refresh your browser or change your pages.

 

The 15min for 1.6M lines seems like something else is going on too. I can use the 12M+ record Contoso database and it takes less than 5Min to fully refresh on my laptop, so it may be your server is part of the speed issue. You can read more about Incremental Refresh here.

 

I appreciate that you are new to Power BI. It is very powerful and will ultimately be a rewarding experience for you. But today I think you are looking at it like Excel where you have Excel and that is your data. Power BI is not Power BI Desktop. Power BI is the Power BI Desktop app, workspaces in the service, scheduled refresh settings, Reports, Apps, Sharing, and more. When you limit yourself to just the desktop app, you are hitting the roadblocks you are getting frustrated with. Power BI Desktop is not designed to be the end user analysis tool. The service is. But you have to understand how most of the pieces fit together. One place to get started is here. There are books about Power BI you could read. One is by Reza Rad called From Rookie to Rockstar. It is so massive it is 4 books, but also free. The first book has a lot of info on the service, talks about workspaces, refreshes, etc.

 

There are videos too, and for pay courses. They will help you see more clearly than I can in a community post how all of the pieces fit together.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I really appreciate your time and all the detailed explaniations and resources you have shared. 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Awesome! Thanks a lot @edhans, really appreciate the advice. 👍

gaiusgw
Helper II
Helper II

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. 

Capture.JPG

 

OPENDET is where I need to add the info to. 

2.JPG

 

OPENHEAD is where I need to pull the info from. I need the info in the BULK column. The common link is CONTROL_NO. 

3.JPG

 

Here you can see when I attempt to add a column and use LOOKUPVALUE, it is not recognized. 

4.JPG5.JPG

 

Is there a way for me to pull the BULK info from OPENHEAD to add a column in OPENDET in transform data view? 

Anonymous
Not applicable

You can create calculated column in Table 1 using following DAX formula :

 

BULK = LOOKUPVALUE(TABLE2[CONTROL_NOBULK],TABLE2[ID],TABLE1[Id])

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. 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors