Power Query : MIN.SI.ENS equivalent ?

[FDANIEL]

Hello everyone,

I called the community a few days ago with success, that's why I come back to you today.

I have a small question about Power QUERY.

Is there an equivalent of the "MIN.SI.ENS" (French version) function in Power Query ?

Let me explain :

I have two tables:
- CN
- Supply

In these two tables, there is a column "Item Key" which allows to make the link between the two. And a second column with quantities.

I give you the Excel function that I have to translate in M : MIN.SI.ENS(Supply[Qté cumul Art_Site];Supply[Qté cumul Art_Site];">="&[@[Qté cumul art_site]];Supply[Clé Art_Site];[@[Clé Art_Site]])

The result must appear in the table "CN".

I specify that I must absolutely do it in M I must not pass by DAX.

I hope you will understand my request and I thank you in advance for your help.

Sincerely,

FDANIEL

[jbwtp]

Hi @FDANIEL,

Assuming that I correctly understand what the function does:

The MIN.SI. ENS returns the minimum value among the cells specified by a set of conditions or criteria.

and the logic of your query, this is something that you can try:

Assuming that you have CN table:

and Supply table:

Merge Supply table to CN table using LeftJoin:

Expand the Supply column:

In the dialog switch to Aggregate option and select Minimum from the drop-down list.

This is the output that you should get:

Kind regards,

John

[FDANIEL]

Hello,

thank you for your help.

Your answer is very close to what I am looking for but in my CN table there is a column of quantities and in the end I have to retrieve the minimum quantity from the other table among those that are >= to the quantity of the CN table.

Example of the desired result with a sample of my data

CN : 

Item Key CN	CN Quantity	Desired result
000040_12D18	1898,4	2000
000073_12D18	292,5	489,4
000075_12D18	376,5	526,4
000078_12D18	3,3	119,2
000079_12D18	0	604,6
000152_12D00	0	63
000152_12D00	0	63
000152_12D00	0	63
000152_12D00	0	63
000152_12D00	0	63
000152_12D00	0	63
000152_12D00	0	63
000152_12D00	0	63
000158_12D18	28	128
000158_12D18	128	128
000158_12D18	228	228
000182_12D18	100	100
000197_12D00	0	2
000197_12D00	0	2
000197_12D00	0	2
000197_12D00	0	2

Supply :

Item Key Supply	Supply Quantity
000040_12D18	0
000040_12D18	2000
000073_12D18	0
000073_12D18	489,4
000075_12D18	0
000075_12D18	526,4
000078_12D18	0
000078_12D18	119,2
000079_12D18	604,6
000152_12D00	63
000158_12D18	0
000158_12D18	128
000158_12D18	228
000182_12D18	100
000197_12D00	2

Sincerely,

FDANIEL

[jbwtp]

Hi @FDANIEL,

Like this?

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),
    Output = Table.AddColumn(#"Merged Queries", "Actual Result", (x)=> List.Min(List.Select(x[Supply][Supply Quantity], each Number.FromText(_, "de-DE")>=Number.FromText(x[CN Quantity], "de-DE"))))
in
    Output

Kind regards,

John

[FDANIEL]

Hello,

Your solution works but this step requires a lot of resources when I apply it on my data.

In Power Query it takes several minutes to load and when I apply it in Power BI the loading never finishes.

Is there a way to optimize what you sent me ?

Thank you for your help.
Have a nice day,

FDANIEL

[jbwtp · Accepted solution]

Hi @FDANIEL,

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

[FDANIEL]

Hello,

I have applied your solution and it works perfectly

Thank you very much for your help

Have a nice day,

FDANIEL