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.
Hi.
This is my issue:
Product | Delivery Date Changed On | Old Delivery Date |
A | 2/1/20 | 5/15/20 |
A | 3/1/20 | 5/20/20 |
A | 4/1/20 | 5/16/20 |
The output I am looking for is
Hence Grouping Min and then All Rows do not give me the answer.
Any suggestions? Table function ? List function?
Thank you for your help
Solved! Go to Solution.
Hello @JMSNYC
you can use Table.Group and use a Table.SelectRows in it.
Here a complete example. Check out the step #"Grouped rows" that does the trick
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLSN9Q3MgAyTPUNTUGsWB2IhDFCwsgAWcIESYcZWCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Delivery Date Changed On" = _t, #"Old Delivery Date" = _t]),
DateFormat = Table.TransformColumns
(
Source,
{
{
"Delivery Date Changed On",
each Date.From(_,"en-US"),
type date
},
{
"Old Delivery Date",
each Date.From(_,"en-US"),
type date
}
}
),
#"Grouped Rows" = Table.Group(DateFormat, {"Product"}, {{"Min Date", each Table.SelectRows(_, (sel)=> sel[Delivery Date Changed On]= List.Min(_[Delivery Date Changed On])), type table [Product=text, Delivery Date Changed On=date, Old Delivery Date=date]}}),
#"Expanded Min Date" = Table.ExpandTableColumn(#"Grouped Rows", "Min Date", {"Delivery Date Changed On", "Old Delivery Date"}, {"Delivery Date Changed On", "Old Delivery Date"})
in
#"Expanded Min Date"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @JMSNYC
you can use Table.Group and use a Table.SelectRows in it.
Here a complete example. Check out the step #"Grouped rows" that does the trick
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLSN9Q3MgAyTPUNTUGsWB2IhDFCwsgAWcIESYcZWCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Delivery Date Changed On" = _t, #"Old Delivery Date" = _t]),
DateFormat = Table.TransformColumns
(
Source,
{
{
"Delivery Date Changed On",
each Date.From(_,"en-US"),
type date
},
{
"Old Delivery Date",
each Date.From(_,"en-US"),
type date
}
}
),
#"Grouped Rows" = Table.Group(DateFormat, {"Product"}, {{"Min Date", each Table.SelectRows(_, (sel)=> sel[Delivery Date Changed On]= List.Min(_[Delivery Date Changed On])), type table [Product=text, Delivery Date Changed On=date, Old Delivery Date=date]}}),
#"Expanded Min Date" = Table.ExpandTableColumn(#"Grouped Rows", "Min Date", {"Delivery Date Changed On", "Old Delivery Date"}, {"Delivery Date Changed On", "Old Delivery Date"})
in
#"Expanded Min Date"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
This is AMAZING !! Works perfectly. Thanks a lot and many kudos.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.