Converting Dax into PowerQuery (M Language)

[Citegetse]

Hi folks , 

I hope this finds you'all well.

I have the following PowerBI Desktop Dax code, I would like to have its corresponding in PowerQuery (M Langue version) of it or Dax Version which works in the Datamart. Note this code does not work in  the datamart (DAX) because you can ONLY create measures, columns. To create columns you need to use PowerQuery.

MaxPeriodValue =

'TableName'[Posting_Key] = CALCULATE(MAX('TableName'[Fiscal Period]),ALLEXCEPT('TableName','TableName'[FiscalYear]))

TableName contains data for mutiple fiscal years. For each fiscal year, there are multiple Fiscal periods and mulitple Posting Keys. 

What I need is Yes/No answer. 

-The first steps is to calucalte the Max value of all the fiscal periods for all the fiscal years.

-The second step  is to compare each value in the Posting_key  column. If any of the value in the Posting_key column = Max(Fiscal Period) then the MaxPeriodValue = true else it is false. 

For instance if the Max(Fiscal Period =14) and I have value in the posting_key which is 14,  I will have the following value in the table: 

I would appreciate your help, thanks.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fiscal period", Int64.Type}, {"Max (Fiscal Period )", Int64.Type}, {"Posting_Key", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max({[#"Max (Fiscal Period )"]})=[Posting_Key])
in
    #"Added Custom"

Hope this helps.

[Citegetse]

Hi Ashish , 

Thanks so much for your response. I tried this solution, it works  fine for few data. Maybe I forgot to mentionne that , but I have over 15millions of reocords . 

When the solution is applied for one fiscal year's data, it works. But when it is applied on a bigger dataset with all fiscal years' data , the load times out. I was looking for another solution. 

or better way to say it , what do I do to avoid timeout during the evaluation process for a bigger dataset? 

[Ashish_Mathur]

You are welcome.  I am not aware of any other way.