How to do a 'vlookup' from one table to another

[CloudMonkey]

Hi,

I'm a bit confused how to go about doing a 'sumifs' calculated column from one table to another, while keeping things simple enough in a company with no IT/BI department (basically we are self-taught accountants using Power BI). The problem is general but the specific problem we are currently working on is calculating the number of working days a contract takes to be completed. So we need to 'sumifs' a calendar table that already has working day flags if each date is greater or equal to the project start date and less than or equal to the project end date.

The 2 approaches I'm aware are both impractical for our company (are there better options)?:

1. In the normal data view using simple DAX code (maybe combination of FILTER and COUNT). But the calculated column won't be available in the "Edit queries" window and so it won't be possible to use the calculated column when appending/merging tables.

2. Create the calculated column in the Edit Queries window (Power Query) using complicated M code (like here -> http://community.powerbi.com/t5/Desktop/Power-Query-M-code-for-referring-to-another-table/m-p/287735) but this too complicated for an accounts department to handle reliably (we don't have an IT/BI department so are relying on self-taught accountants)

So currently I'm thinking Power BI isn't practical for a small accounts department without IT/BI department support? Can anyone persuade me otherwise?

Thanks,

CM

[CloudMonkey]

Hi I'm not sure if my post made sense. Was my summary fair, or did I miss better way of doing this?

[Ffitzpatrick47 · Accepted solution]

The easiest way to do a lookup from one table to another is to do a vlookup from one table to another. Dax and power bi can do it, but it doesn't make it easier.  It makes it much much harder in exchange for a little more power you'll probably not use. Companies would rather pay IBM oracle sap whatever hundreds of thousands for cognos obiee and crystal not to mention tableau, python pandas etc because they are all much much easier than the free science project msft kludged into excel. So make your two tables, one on each tab. Do your vlookup. Then add them both to the data model. And see if it joins. 

[CloudMonkey]

Thank you @Ffitzpatrick47, that sounds like a pragmatic solution