Forum Discussion

SaaM's avatar
SaaM
Helper II
5 years ago
Solved

Equivalent DAX Formula in Power query ?

Hi all,   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( MINX( Filter( Table, and( Tabl...
Need Help
  • stevedep's avatar
    stevedep
    5 years ago

    Hi,

     

    It can be done like this:

    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"

    Hope it helps.

    Kind regards, Steve. 

     

JW_van_Holst's avatar
JW_van_Holst
Resolver IV
5 years ago

Hope I understood your problem.

First: be sure you have your table sorted on item and LineNumber.
Second: make a duplicate an groupby item and aggretate Max item

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzgLBMwywnIMoWzzOAsc6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, LineNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"LineNumber", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {{"Max", each List.Max([LineNumber]), type nullable number}})
in
    #"Grouped Rows"

Third add following columns to the original table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzgLBMwywnIMoWzzOAsc6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, LineNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"LineNumber", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item", "LineNumber"}, DuplicateTable, {"Item", "Max"}, "DuplicateTable", JoinKind.LeftOuter),
    #"Expanded DuplicateTable" = Table.ExpandTableColumn(#"Merged Queries", "DuplicateTable", {"Max"}, {"Max"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded DuplicateTable", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Column", each if [Max] <> null then [LineNumber] else Table{[Index]+1}[LineNumber], type number)
in
    #"Added Custom"

You will have the following result:

 

SaaM's avatar
SaaM
Helper II
5 years ago

Thanks JW_van_Holst for the alternative but stevedep's solution works but it is too much consuming for a table with more than 10k lines and articles ...
Is there anyway to optimise the solution ?

Thanks

  • JW_van_Holst's avatar
    JW_van_Holst
    Resolver IV
    5 years ago

    This solution generates a list per item and then selects the item from that list. This reduces the number of operations.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiOlWB0IzwSMYTwjIDaG84zhck5AlikQm8F5ZkBsDueZQ3ixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, LineNumber = _t, ColumDesired = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"LineNumber", Int64.Type}, {"ColumDesired", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Item", Order.Ascending}, {"LineNumber", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Item"}, {{"Data", each _, type table [Item=nullable text, LineNumber=nullable number, ColumDesired=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DataWithIndex", each Table.AddIndexColumn([Data], "Index")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"DataWithIndex"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "TempColumn", each List.RemoveFirstN([DataWithIndex][LineNumber]) & {List.Last([DataWithIndex][LineNumber])}),
    #"Expanded DataWithIndex" = Table.ExpandTableColumn(#"Added Custom1", "DataWithIndex", {"Item", "LineNumber", "ColumDesired", "Index"}, {"Item", "LineNumber", "ColumDesired", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded DataWithIndex", "Column", each [TempColumn]{[Index]})
in
    #"Added Custom2"