Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a mock data table like this:
Item | Purchase Order number | Ship date | Quantity |
A | ANR1 | 3/2/2020 | 4 |
A | ANR2 | 3/5/2020 | 5 |
B | ANR5 | 6/5/2020 | 3 |
B | ANR9 | 5/4/2020 | 4 |
B | ANR4 | 4/23/2020 | 4 |
C | ANR8 | 5/12/2020 | 12 |
I want to group by Item, then get me the earliest Ship date, and the accordingly quantity of that shipment. Result should look like this:
Item | Ship date | Quantity |
A | 3/2/2020 | 4 |
B | 4/23/2020 | 4 |
C | 5/12/2020 | 12 |
Group by function in Power Query let you do all the aggregating fun stuff. I do need that to get out the Min (Ship date), but after that i just want to grab the Qty without any aggregation ( to prevent case that there are 2 orders ship on same date with different quantity). Any advice? Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous ,
Doing it via grouping is a bit clunky in my eyes, so you can also check out the version via duplicate removal:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc49DoAgDAXgu3Qm6Q+t0RHdHVwJ97+GYJHA1PR96UtzhgQB0v1wHRSRBIWE6qJQwlDpar/ap6erNd0mjbMeTQ1J1+au2gKUuOLluPspj6dYoJQX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Purchase Order number" = _t, #"Ship date" = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Purchase Order number", type text}, {"Ship date", type date}, {"Quantity", Int64.Type}}, "en-us"),
GroupWithAddedPartition = Table.Group(#"Changed Type", {"Item"}, {{"Earliest Date", each List.Min([Ship date]), type date}, {"ItemPartition", each _, type table [Item=text, Purchase Order number=text, Ship date=date, Quantity=number]}}),
FilterOnEarliestDate = Table.AddColumn(GroupWithAddedPartition, "FilterEarliestDate", each Table.SelectRows([ItemPartition], (x) => x[Ship date] = [Earliest Date])),
GetQuantity = Table.AddColumn(FilterOnEarliestDate, "GrabQuantityColumn", each [FilterEarliestDate][Quantity]{0}),
Cleanup = Table.RemoveColumns(GetQuantity,{"ItemPartition", "FilterEarliestDate"})
in
Cleanup
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc49DoAgDAXgu3Qm6Q+t0RHdHVwJ97+GYJHA1PR96UtzhgQB0v1wHRSRBIWE6qJQwlDpar/ap6erNd0mjbMeTQ1J1+au2gKUuOLluPspj6dYoJQX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Purchase Order number" = _t, #"Ship date" = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Purchase Order number", type text}, {"Ship date", type date}, {"Quantity", Int64.Type}}, "en-us"),
SortByShipDate = Table.Buffer( Table.Sort(#"Changed Type",{{"Ship date", Order.Ascending}}) ),
#"Removed Duplicates" = Table.Distinct(SortByShipDate, {"Item"})
in
#"Removed Duplicates"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous ,
Doing it via grouping is a bit clunky in my eyes, so you can also check out the version via duplicate removal:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc49DoAgDAXgu3Qm6Q+t0RHdHVwJ97+GYJHA1PR96UtzhgQB0v1wHRSRBIWE6qJQwlDpar/ap6erNd0mjbMeTQ1J1+au2gKUuOLluPspj6dYoJQX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Purchase Order number" = _t, #"Ship date" = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Purchase Order number", type text}, {"Ship date", type date}, {"Quantity", Int64.Type}}, "en-us"),
GroupWithAddedPartition = Table.Group(#"Changed Type", {"Item"}, {{"Earliest Date", each List.Min([Ship date]), type date}, {"ItemPartition", each _, type table [Item=text, Purchase Order number=text, Ship date=date, Quantity=number]}}),
FilterOnEarliestDate = Table.AddColumn(GroupWithAddedPartition, "FilterEarliestDate", each Table.SelectRows([ItemPartition], (x) => x[Ship date] = [Earliest Date])),
GetQuantity = Table.AddColumn(FilterOnEarliestDate, "GrabQuantityColumn", each [FilterEarliestDate][Quantity]{0}),
Cleanup = Table.RemoveColumns(GetQuantity,{"ItemPartition", "FilterEarliestDate"})
in
Cleanup
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc49DoAgDAXgu3Qm6Q+t0RHdHVwJ97+GYJHA1PR96UtzhgQB0v1wHRSRBIWE6qJQwlDpar/ap6erNd0mjbMeTQ1J1+au2gKUuOLluPspj6dYoJQX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Purchase Order number" = _t, #"Ship date" = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Purchase Order number", type text}, {"Ship date", type date}, {"Quantity", Int64.Type}}, "en-us"),
SortByShipDate = Table.Buffer( Table.Sort(#"Changed Type",{{"Ship date", Order.Ascending}}) ),
#"Removed Duplicates" = Table.Distinct(SortByShipDate, {"Item"})
in
#"Removed Duplicates"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF
Superb! Works like a charm!
Since you're here 😆 What do you suggest to not only sort out the date that is the earliest ship date, but also filter that that date is a future ship date (>=today). I thought about adding a column with DateTime.LocalNow(), then compare ship date with that and mark each line either "Past shipment" or "Future shipment" and then filter for just "Future shipment" and from there use your solution to get the earliest. Any word of advice? Thanks alot!
Hi @Anonymous ,
yes, that would be a solution.
But you could also "simply" filter out future ship dates on the date column itself with relative date filters. Same effect: sned just future transactions to the subsequent steps, but no need for additional column. But that might be only cosmetic and doesn't effect the query performance.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |