Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi,
I have a file that lists the Field Ticket Number and Modified Date. I would like to create a new field called FirstModifiedDate.
One ticket number can have multiple line items:
Ticket# ModifiedDate
181234 12/5/2019
181234 12/4/2019
181234 12/5/2019
181235 12/5/2019
181235 12/4/2019
181235 12/3/2019
I would like my output as follows:
Ticket# ModifiedDate FirstModifiedDate
181234 12/5/2019 12/4/2019
181234 12/4/2019 12/4/2019
181234 12/5/2019 12/4/2019
181235 12/5/2019 12/3/2019
181235 12/4/2019 12/3/2019
181235 12/3/2019 12/3/2019
What do I need to do in Power Query?
Solved! Go to Solution.
Hi @Nusc ,
We can use group for one time and then expand the result to meet your requirement:
All the quries are here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQwNDI2UdJRMjTSN9U3MjC0VIrVQRU2wS6MrtoUrzC6IVBhY6hwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Ticket#" = _t, ModifiedDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket#", Int64.Type}, {"ModifiedDate", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket#"}, {{"Data", each _, type table [#"Ticket#"=number, ModifiedDate=date]}, {"FirstModifiedDate", each List.Min([ModifiedDate]), type date}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"ModifiedDate"}, {"Data.ModifiedDate"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data",{{"Data.ModifiedDate", "ModifiedDate"}})
in
#"Renamed Columns"
Best regards,
Hi @Nusc ,
We can use group for one time and then expand the result to meet your requirement:
All the quries are here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQwNDI2UdJRMjTSN9U3MjC0VIrVQRU2wS6MrtoUrzC6IVBhY6hwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Ticket#" = _t, ModifiedDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket#", Int64.Type}, {"ModifiedDate", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket#"}, {{"Data", each _, type table [#"Ticket#"=number, ModifiedDate=date]}, {"FirstModifiedDate", each List.Min([ModifiedDate]), type date}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"ModifiedDate"}, {"Data.ModifiedDate"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data",{{"Data.ModifiedDate", "ModifiedDate"}})
in
#"Renamed Columns"
Best regards,
I had no idea this was possible....I just did a calculated column.
Hello @Nusc
for this you need some coding and use a Table.Group with List.Min function and a NestedJoin to join the result to your original table.
Here the complete solution
let
Source = #table
(
{"Ticket#","ModifiedDate"},
{
{"181234","43597"}, {"181234","43567"}, {"181234","43597"}, {"181235","43597"}, {"181235","43567"}, {"181235","43536"}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"ModifiedDate",
each Date.From(Number.From(_)),
type date
}
}
),
Group = Table.Group
(
ToDate,
{"Ticket#"},
{
{
"MaxDate",
each List.Min(_[ModifiedDate]),
type date
}
}
),
NestedJoin = Table.NestedJoin
(
ToDate,
"Ticket#",
Group,
"Ticket#",
"MaxDate"
),
Expand = Table.ExpandTableColumn(NestedJoin, "MaxDate", {"MaxDate"}, {"MaxDate"})
in
Expand
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
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
Was I not clear?
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
9 |