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.
Hey Everyone!
I'm working on a project where I need to show employee training and their Expiration/Due Date. I am almost finished, but I'm having an issue where users have two of the same Training with different due dates (attached). Is there a way that i can remove the training with the old date and keep the latest date without having to delete each row one by one?
Apologies, I've only been doing Power Query/BI for about 2 months now! Any help is appreciated!
Solved! Go to Solution.
@aa_lovee You can first group the data by the columns and identify the max date and then remove everything else as shown in the below code, create a new query and paste it into Advanced Editor.
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"jZJRb4IwFIX/SsOzCW1xCI8VN0eEYRR8IT4gIRsZQkJHlv373TIMLcW4J6B895zbc2+aGnVXVcbC8IIQYwtehm/XpCuTYmoZ50Vq7P1dEsBpFL8+H+CZV1fxa2EQkxCBEQWbigFlCYpOKA8vbYWa0bpZbvceKmv00XS8QHnRfsEZiEIBsWcLOo4uWf3ZC9MnXfiFgf1Ksgd/Z7xyFPgntolif9JDXmWct01z/fPvG3C0imkC9KF2ckRr9raTWHKXFepD/Mt50gu2GBPFfzVOQCW1Kdj/1exBrIFasoA9uNDPpWgR+87aoi44R32nUEhNZ77rW+GGJShus7Iu6/eboWBP0Wy00Ik9CiqQmK0XsOPxEEWhQKFlaR0Vdswfm8SVrpZ4OxayuQhov6oaN81UuN4B1YWyhjHRCXdvVYWw2BXiasLq/C11/prwGKZKwoGcjPTz/As=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [
#"Appointment Last Name" = _t,
#"Training Code" = _t,
#"Training Other" = _t,
#"Training Due Date" = _t
]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{
{ "Appointment Last Name", type text },
{ "Training Code", type text },
{ "Training Other", type text },
{ "Training Due Date", type date }
}
),
GroupedRows =
Table.Group (
ChangedType,
{ "Appointment Last Name", "Training Code", "Training Other" },
{
{
"Transformation",
( CurrentGroup ) =>
Table.SelectRows (
CurrentGroup,
each [Training Due Date] = List.Max ( CurrentGroup[Training Due Date] )
),
type table [
Appointment Last Name = nullable text,
Training Code = nullable text,
Training Other = nullable text,
Training Due Date = nullable date
]
}
}
),
RemovedOtherColumns = Table.SelectColumns ( GroupedRows, { "Transformation" } ),
ColumnNames = Table.ColumnNames ( RemovedOtherColumns[Transformation]{0} ),
ExpandedCount =
Table.ExpandTableColumn (
RemovedOtherColumns,
"Transformation",
ColumnNames,
ColumnNames
)
in
ExpandedCount
@aa_lovee You can first group the data by the columns and identify the max date and then remove everything else as shown in the below code, create a new query and paste it into Advanced Editor.
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"jZJRb4IwFIX/SsOzCW1xCI8VN0eEYRR8IT4gIRsZQkJHlv373TIMLcW4J6B895zbc2+aGnVXVcbC8IIQYwtehm/XpCuTYmoZ50Vq7P1dEsBpFL8+H+CZV1fxa2EQkxCBEQWbigFlCYpOKA8vbYWa0bpZbvceKmv00XS8QHnRfsEZiEIBsWcLOo4uWf3ZC9MnXfiFgf1Ksgd/Z7xyFPgntolif9JDXmWct01z/fPvG3C0imkC9KF2ckRr9raTWHKXFepD/Mt50gu2GBPFfzVOQCW1Kdj/1exBrIFasoA9uNDPpWgR+87aoi44R32nUEhNZ77rW+GGJShus7Iu6/eboWBP0Wy00Ik9CiqQmK0XsOPxEEWhQKFlaR0Vdswfm8SVrpZ4OxayuQhov6oaN81UuN4B1YWyhjHRCXdvVYWw2BXiasLq/C11/prwGKZKwoGcjPTz/As=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [
#"Appointment Last Name" = _t,
#"Training Code" = _t,
#"Training Other" = _t,
#"Training Due Date" = _t
]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{
{ "Appointment Last Name", type text },
{ "Training Code", type text },
{ "Training Other", type text },
{ "Training Due Date", type date }
}
),
GroupedRows =
Table.Group (
ChangedType,
{ "Appointment Last Name", "Training Code", "Training Other" },
{
{
"Transformation",
( CurrentGroup ) =>
Table.SelectRows (
CurrentGroup,
each [Training Due Date] = List.Max ( CurrentGroup[Training Due Date] )
),
type table [
Appointment Last Name = nullable text,
Training Code = nullable text,
Training Other = nullable text,
Training Due Date = nullable date
]
}
}
),
RemovedOtherColumns = Table.SelectColumns ( GroupedRows, { "Transformation" } ),
ColumnNames = Table.ColumnNames ( RemovedOtherColumns[Transformation]{0} ),
ExpandedCount =
Table.ExpandTableColumn (
RemovedOtherColumns,
"Transformation",
ColumnNames,
ColumnNames
)
in
ExpandedCount
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.