Power Query : MIN.SI.ENS equivalent ?

Hi Anonymous,

This is why I don't like joins. I think what is happening for each line in the CN table the merging table (Supply) is getting recalculated, which causes a lot of CPU load. The way around it may be to Table.Buffer the supply table before join/marging with CN table (if this is resonably small).

Also try this version, it may run faster as it should eliminate multiple calls to the Supply table.

let
    Supply = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZA7DsAgCEDv4swA+IO9tzCm979F6VAhKW6+96KEtQraaXgTXyQFCpYNP8h2PXzWJA6wiUJz0bPaYecRa8lqh0QK7EKPGNhgfII6v8JmNlGdJo9HSCwZ5oCFvfaVkM7zn023Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Key Supply" = _t, #"Supply Quantity" = _t]),
    CN = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZE9DsMwCEbv4pkBPv/BnltEUe9/ixJHNqlUZepSS8bI71kgvO+JfRV+CTbRREnUlIon8Pt00CX0vAQYqPpZ1NxbQl1C7m0IFe0uaAiUz0JihMC2MPtuXKhNKBUnZJ4w/zuJWWDM3OMXJk8QmHFCRbwcVSU+UKx/NIOfguMN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Key CN" = _t, #"CN Quantity" = _t, #"Desired result" = _t]),
    #"Merged Queries" = Table.NestedJoin(CN, {"Item Key CN"}, Supply, {"Item Key Supply"}, "Supply", JoinKind.LeftOuter),
    #"Expanded Supply" = Table.ExpandTableColumn(#"Merged Queries", "Supply", {"Supply Quantity"}, {"Supply Quantity"}),
    fMatch = (t as table) => 
        let 
            mTable = Table.Buffer(t),
            selectRows = Table.SelectRows(mTable, each Number.FromText([Supply Quantity], "de-DE")>=Number.FromText([CN Quantity], "de-DE")),
            selectValue = List.Min(selectRows[Supply Quantity], 0)
        in selectValue,
    #"Grouped Rows" = #"Expanded Supply",
    #"Grouped Rows1" = Table.Group(#"Expanded Supply", {"Item Key CN", "CN Quantity", "Desired result"}, {{"Supply Quantity", fMatch, type text}})
in
    #"Grouped Rows1"

Kind regards,

John