Forum Discussion
Select newest data in column
Hi everyone.
I need a help with writing measure or creating table to solve my problem.
I have a table, where data is in flat format.
| Date | Name | Group | Value |
| 1 Jan | Name1 | A | 1 |
| 3 Jan | Name1 | A | 2 |
| 4 Jan | Name1 | B | 4 |
| 4 Jan | Name2 | B | 3 |
Then I do Pivot.
| Date | Name | A | B |
| 1 Jan | Name1 | 1 | |
| 3 Jan | Name1 | 2 | |
| 4 Jan | Name1 | 4 | |
| 4 Jan | Name2 | 3 |
But in my table data is inserted only when something has been changed, for example value for some group.
So I need a measure or table that for each name show the newest data in a column:
| Name | A | B |
| Name1 | 2 | 4 |
| Name2 | 3 | 3 |
BTW, I have 140 columns after pivoting, if it`s important.
So it`s very similar to usual pivoting, but aggregation function is to select newest data.
Would be very appreciated for any help.
Hi Anonymous
You can filter the table to keep only rows with the newest data, then do pivot as you did. Below are steps.
Firstly, group the table by Name and Group column, aggregating All Rows into a column AllData.
Secondly, add a custom column to filter each table value in AllData column. In this way, each table value will remain only the newest data row.
Thirdly, remove columns except for Custom column. Then expand Custom column. You will get a filtered table that only remains the newest data. You can pivot it as you did.
You can paste below codes into a blank query to see all steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ11zVU0lHyS8xNBdGOQGyoFKsDlzRGkzRCljRBknQCYhNskkZQSWNckiBjTZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Group = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Group", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Group"}, {{"AllData", each _, type table [Date=nullable date, Name=nullable text, Group=nullable text, Value=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let newestDate = List.Max([AllData][Date]) in Table.SelectRows([AllData],each [Date]=newestDate)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Name", "Group", "Value"}, {"Date", "Name", "Group", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Group]), "Group", "Value", List.Sum) in #"Pivoted Column"Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
4 Replies
Anonymous you don't need to pivot the data, keep it unpivoted, all following two measures:
Latest Value = VAR __t = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[ Value] ), 'Table'[Date] = __t ) New Value = SUMX ( SUMMARIZE ('Table', 'Table'[ Name ], 'Table'[Group] ), [Latest Value] )On matrix visual, use Name on rows Group on columns and New Value measure on value and you will get the output.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi Anonymous
You can filter the table to keep only rows with the newest data, then do pivot as you did. Below are steps.
Firstly, group the table by Name and Group column, aggregating All Rows into a column AllData.
Secondly, add a custom column to filter each table value in AllData column. In this way, each table value will remain only the newest data row.
Thirdly, remove columns except for Custom column. Then expand Custom column. You will get a filtered table that only remains the newest data. You can pivot it as you did.
You can paste below codes into a blank query to see all steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ11zVU0lHyS8xNBdGOQGyoFKsDlzRGkzRCljRBknQCYhNskkZQSWNckiBjTZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Group = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Group", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Group"}, {{"AllData", each _, type table [Date=nullable date, Name=nullable text, Group=nullable text, Value=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let newestDate = List.Max([AllData][Date]) in Table.SelectRows([AllData],each [Date]=newestDate)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Name", "Group", "Value"}, {"Date", "Name", "Group", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Group]), "Group", "Value", List.Sum) in #"Pivoted Column"Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
