Translating a DAX Function to Power Query

[Anonymous]

I'm working with tables that I need to join based on a calculated field. I know how to do the calculation in DAX, however, this doesn't help me merge the tables (I need to join on multiple columns so relationships won't work). I know if I can get the following formula into Power Query, I will be able to merge the tables I'd like to but I just can't quite get the formula right. This is what I have in DAX in Table1 and it does exactly what I need it to:

Date = Calculate(Max(Table2[Effective_Term]),Filter('Table2','Table1'[Term]=>'Table2'[Effective_Term]))

Essentially I have two date columns ("Effective_Term" in Table2 and "Term" in Table1) and I need to find the maximum Effective_Term that is less than the Term.For example, Effective_Term contains {201802, 201701, 201601} so for Term=201801 I want to return 201701). 

Any help is appreciated!! 

[CheenuSing]

Hi @Anonymous

Can you please share data in OneDrive or GoogleDrive and post the link here to find a solution.

Cheers

CheenuSing

[Anonymous]

I posted some sample data here:

https://hofstra1edu-my.sharepoint.com/:f:/g/personal/kaitlyn_poncet_hofstra_edu/EpPfRjUFTgVJmsNiJmnp...

"Course Info" only contains the Effective Term and not the TermCode, so I want to add a column in the "Term Info" table  (in query editor) that related TermCode and EffectiveTerm. Then, I would be able to pull in the related TermCode for each EffectiveTerm in "Course Info" and merge "Course Info" and "Scheduling Data" on CourseID and TermCode. 

I have the result I would like to have in DAX (see "Term Info", FORMULA_EFF_TERM), however, in order to accompish the merge I need this result column in query mode. Hopefully I explained this clearly but if not please let me know! 

[CheenuSing]

Hi @Anonymous

Please let me know if my understanding is right

You want the value of "Term Info"[FORMULA_EFF_TERM] value to be posted in the Course Info table as a new column.

Cheers

CheenuSing

[Anonymous]

I'd like to have the "Term Info"[FORMULA_EFF_TERM] values to appear in query editor so I'm able to merge tables (I need to link on more than one value.