Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 "Cu...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
edhans
Community Champion
6 years ago

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