Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replicating Dax in Power Query

Hello, 

 

I need to replicate this DAX formula in Power Query, as I need to remove the blank rows, However my DAX calculated collum doesnt appear in the Query Editor. 

 

New Collum =

VAR LatestDate =


MAXX(FILTER('EmployeeJobs','EmployeeJobs'[empid] = EARLIER(EmployeeJobs[empid])),EmployeeJobs[datefrom])


RETURN IF('EmployeeJobs'[datefrom] = LatestDate ,EmployeeJobs[empid], BLANK())

 

I'd Appreciate any advice

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hello both, 

     

    Thanks for your responses, I managed a work around which solves my problem. 

     

    Thanks

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    You can recreate your DAX calculated column in Power Query.

    Can you provide a data sample and desired outcome?

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.


     

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Howardd, 

    You could refe to below Mcode to see whether it work or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMLTUN9Q3VIrVQRIwQhcwhgoYwQRMMQXMwQLGCAETVAFDA31TVBEzkCGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"MAX", each List.Max([date]), type date}, {"ALL", each _, type table [id=number, date=date]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"date"}, {"ALL.date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded ALL", "Custom", each if [MAX]=[ALL.date] then [id] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MAX"})
in
    #"Removed Columns"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello both, 

     

    Thanks for your responses, I managed a work around which solves my problem. 

     

    Thanks