Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I need to have a new desc column based on the latest data.
For ex: If ID is 1363 then new Desc is CC ( Max date 1/20/2021 9:11:12 PM)
Please help me to achieve this. Please find the sample data and resultant output.
Solved! Go to Solution.
Hi @Kavya123 ,
Please try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2M1bSUXJ0dASSRgZGBrqWusYGSrE6cCknJyeIlKEuEFoiSzk7I2SMUDS5uLjAzLPQNTJDlnJzc8NlnqurK6qBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Desc = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Desc", type text}, {"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Max", each List.Max([Date]), type nullable date}, {"S", each _, type table [id=nullable number, Desc=nullable text, Date=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows(
[S],
(itable) => itable[Date]=[Max])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Desc"}, {"Desc"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"S"})
in
#"Removed Columns"
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Kavya123 ,
Please try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2M1bSUXJ0dASSRgZGBrqWusYGSrE6cCknJyeIlKEuEFoiSzk7I2SMUDS5uLjAzLPQNTJDlnJzc8NlnqurK6qBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Desc = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Desc", type text}, {"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Max", each List.Max([Date]), type nullable date}, {"S", each _, type table [id=nullable number, Desc=nullable text, Date=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows(
[S],
(itable) => itable[Date]=[Max])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Desc"}, {"Desc"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"S"})
in
#"Removed Columns"
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply. I got the solution
User | Count |
---|---|
18 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
23 | |
4 | |
3 | |
2 | |
1 |