Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.