Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
FDANIEL
Frequent Visitor

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

 

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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

View solution in original post

6 REPLIES 6
jbwtp
Memorable Member
Memorable Member

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:

jbwtp_0-1660002535406.png

 

and Supply table:

jbwtp_1-1660002552022.png

Merge Supply table to CN table using LeftJoin:

jbwtp_2-1660002602465.png

Expand the Supply column:

jbwtp_3-1660002641730.png

 

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

jbwtp_4-1660002696008.png

 

This is the output that you should get:

jbwtp_5-1660002722167.png

 

Kind regards,

John

 

 

FDANIEL
Frequent Visitor

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 CNCN QuantityDesired result
000040_12D181898,42000
000073_12D18292,5489,4
000075_12D18376,5526,4
000078_12D183,3119,2
000079_12D180604,6
000152_12D00063
000152_12D00063
000152_12D00063
000152_12D00063
000152_12D00063
000152_12D00063
000152_12D00063
000152_12D00063
000158_12D1828128
000158_12D18128128
000158_12D18228228
000182_12D18100100
000197_12D0002
000197_12D0002
000197_12D0002
000197_12D0002

 

Supply :

Item Key SupplySupply Quantity
000040_12D180
000040_12D182000
000073_12D180
000073_12D18489,4
000075_12D180
000075_12D18526,4
000078_12D180
000078_12D18119,2
000079_12D18604,6
000152_12D0063
000158_12D180
000158_12D18128
000158_12D18228
000182_12D18100
000197_12D002

 

Sincerely,

FDANIEL

jbwtp
Memorable Member
Memorable Member

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
Frequent Visitor

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
Memorable Member
Memorable Member

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
Frequent Visitor

Hello,

 

I have applied your solution and it works perfectly

 

Thank you very much for your help

Have a nice day,

FDANIEL

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors