Forum Discussion
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
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
Output
Kind regards,
John
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
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
Hello,
I have applied your solution and it works perfectly
Thank you very much for your help
Have a nice day,
FDANIEL