Lookup column from another table

[Anonymous]

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

[Anonymous]

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?

[edhans]

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

[v-gizhi-msft]

Hi,

Please try to use SELECTCOLUMNS function.

Here is my test table:

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:

Hope this helps.

Best Regards,

Giotto Zhi

[Greg_Deckler]

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

[amitchandak]

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]

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.