Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Lookup column from another table

Hi, 

 

I want to create a new table in Power BI Desktop that has a column which lookups and returns each value from another column to use as an ID. 

 

For example, Table 1 has column A called "CustomerID". 

 

Is there a way to create Table 2 with a column that looks up the value from CustomerID in Table 1, treating Table 1 as the master data source?

 

An analogy from excel would be creating a formula in Sheet2, which looks up the column values of Sheet 1 and refreshes data as requried based on this forumla =Sheet1!A:A

6 REPLIES 6
Anonymous
Not applicable

Hi all. 

 

Thanks for the posts. Here's a bit more context for the workflow and required output that hopefully helps:

 

I've got a situation in my organisation where every day we receieve a new version of a csv file with columns that stay the same between files describing our existing customers, showing various customer information such as price paid, product holding, new customers v customer cancellations etc. 

 

My plan was to import the new file every day into a source data table, then have a seperate table run calculations off the source file every day so I don't need to write the same DAX formulas every day and create new visualisations (i.e. I'm basically trying to seperate my source data, calculations and visualisations). If I were doing this in excel I'd generally model input sheets of source data, create calculation sheets that reference the source data columns, and then have visualisations that reference the calculations. 

 

Any suggestions on how to achieve this? Perhaps having a dedicated calculations table looking up data from another table isn't the right approach?

 

@Anonymous Not sure I understand what you mean by "I'm basically trying to seperate my source data, calculations and visualisations."

 

I think the issue is you are basing your modeling on how you'd do this in Excel. I wouldn't do that. Here is what I would do based on my understanding of your issue:

  1. Put your CSV files in one folder.
  2. Import those in Power Query via the Folder option
    1. 2020-03-08 06_09_29-Get Data.png
  3. Once there you want to hit "Transform" - not load.
  4. In Power Query, Expand the files. It will do them all at once. Press the double arrow next to the Contents field
    1. 2020-03-08 06_12_40-Combine Files.png
  5. Once Power Query has combined them you should have one huge query that has all of your files. Do any transformations you want - filter, new columns, whatever
  6. Load into Power BI's DAX side.

 

Now when you get new files, you just drop them in that folder and refresh. Updated files will get their data updated, and new files (new months for example) will get added ot the data. You never have to edit any formulas again.

 

By the way, this is now the way I would also do this in Excel since it has Power Query, vs the spreadsheet tab method you were mentioning.



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
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try to use SELECTCOLUMNS function.

Here is my test table:

1.PNG

Then i define a policy, if i want to show the value of 'A10003' and 'A10005', try this calculated table:

Table = DISTINCT(SELECTCOLUMNS(FILTER(Table1,Table1[CustomerID]="A10003"||Table1[CustomerID]="A10005"),"CustomerID",Table1[CustomerID],"Value",Table1[Value]))

The result shows:

2.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

Greg_Deckler
Super User
Super User

I can't exactly follow this, can you provide example data and your expected result? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

You can always create a column in another table by using x functions and filters, even if there is no relation. or using lookup. Just make sure conditions are met. like

New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])


new Column =
LOOKUPVALUE('Table'[Lot No.], 'Table'[Entry Type], "Output", 'Table'[Document No.], firstnonbank('Table'[Document No.],true()))
OR
new Column =
minx(filter('Table', 'Table'[Entry Type]= "Output" && 'Table'[Document No.]= earlier('Table'[Document No.])),'Table'[Lot No.])

 

edhans
Super User
Super User

You cannot do it using a formula like that in Excel, which is just looking in cell A1 of Sheet 1. tables in Power BI have no row reference like that.

 

You could use the LOOKUPVALUE() function that would operate simlarly to how a VLOOKUP in Excel would. See here for documentation of the syntax and an example.

 

It would be better if you could relate the tables through the CustomerID field though, then use RELATED() from the many side to the one side, or RELATEDTABLE() from the one side to the many side if the relationship. RELATED() would also work for a 1:1, but if you have a 1:1, you are better off just merging the tables in Power Query and bringing in one table. the model is cleaner, leaner, and easier to work with for measure creation.



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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.