Forum Discussion

SaaM's avatar
SaaM
Helper II
5 years ago

Equivalent DAX Formula in Power query ?

Hi everybody, It s my first post here.   I am trying to convert a DAX formula in Power Query and so far I have not find the solution   My DAX formula is :         Column = IF( ISBLANK( ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi SaaM ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

 

Best Regards,
Eyelyn Qin

  • SaaM's avatar
    SaaM
    Helper II
    5 years ago

    Hi Anonymous,

     

    The solution you proposed is not fitting 100% to my problem, that's the reason why I didn t accept it as solution.

    The solution in M is as follow:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDA1UNJRMlSK1UHiGqFyzVG5FqhcQwME3xDIN0XloksbGqLx0dQboRsH5McCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Column", Int64.Type}}),
    fnPrevMaxInLine = (tbl as table, item as number, val as number) => let 
        FilterTbl = Table.SelectRows(tbl, each ([Item] = item and [Column] < val)),
        MaxCol = List.Max(FilterTbl[Column])
        in MaxCol,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PrevMax", each fnPrevMaxInLine(#"Changed Type", [Item], [Column]))
in
    #"Added Custom"

     

    Kind regards

     

    Saam

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDA1UNJRMlSK1UHiGqFyzVG5FqhcQwME3xDIN0XloksbGqLx0dQboRsH5McCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Column", Int64.Type}}),
    fnPrevMaxInLine = (tbl as table, item as number, val as number) => let 
        FilterTbl = Table.SelectRows(tbl, each ([Item] = item and [Column] < val)),
        MaxCol = List.Max(FilterTbl[Column])
        in MaxCol,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PrevMax", each fnPrevMaxInLine(#"Changed Type", [Item], [Column]))
in
    #"Added Custom"​