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(
					Table[item] = EARLIER(Table[item]), 
					Table[line_number] > EARLIER(Table[line_number])
					)
				), 
			Table[line_number]
			)
		),
	Table[line_number],
	MINX(
			Filter(
				Table,  
				and(
					Table[item] = EARLIER(Table[item]), 
					Table[line_number] > EARLIER(Table[line_number])
					)
				), 
			Table[line_number]
			)
)

 

 My result for one item is like this:

 

 

Does somebody have the solution please ?

Thanks

 

SaaM

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. 

     

4 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    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"