Forum Discussion
Power Query : MIN.SI.ENS equivalent ?
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
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
6 Replies
Hi Anonymous,
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
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
Hi Anonymous,
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 OutputKind regards,
John