Equivalent DAX Formula in Power query ?

[SaaM]

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

[JW_van_Holst]

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]

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]

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"

[stevedep · Accepted solution]

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.