Forum Discussion
List table columns with latest dates for a column data
Hi Guys, I tried several options, but without good success.
1. I have a table 'Sheet1' with several columns , the first column [date] is a date of the taken value of the column [a/c]. I have different dates for the same [a/c].
* as you see I have value KB (from [a/c]) once dated 25Jul 2023 and second time dated 27Jul2023. Similar with value KC.
2. I have a report, extracting the data from this table .
3. I need to show only once the value from [a/c] , selecting the one with the latest date (the most current) as of today date. As below:
4. I use this resulted table in the report for the other report visualizations .
Any help what col dbe the best way to do it?
Thanks
yes you can please see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RiXZ9KaMiXsxR8a_J?e=4iMBpi
flag = var _maxdate = MAX('table'[date]) VAR _Result = CALCULATE(MAX('table'[date]),ALLEXCEPT('table','table'[a/c])) RETURN IF( _maxdate =_Result ,1,0)
12 Replies
Hi,
This should ideally be done in the Query Editor. Would you be OK with that solution?
As an alternative I can try the solution directly in the Query editor. Can you suggest it ?
Hi,
Share raw data in a format that can be pasted in an MS Excel file and show the expected result.
I did it quite well in the Query editor , but is it possible to make the filtering in the report? (Since I need the full dataset for other charts and calculations).
yes you can please see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RiXZ9KaMiXsxR8a_J?e=4iMBpi
flag = var _maxdate = MAX('table'[date]) VAR _Result = CALCULATE(MAX('table'[date]),ALLEXCEPT('table','table'[a/c])) RETURN IF( _maxdate =_Result ,1,0)
plse see mw video
https://1drv.ms/v/s!AiUZ0Ws7G26RiXW5HV3zH1GMOE-u?e=UbIUZY
let Source = Excel.Workbook(File.Contents("C:\Users\User\Downloads\Book1.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"a/c", type text}, {"B1", Int64.Type}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"a/c"}, {{"Count", (x)=> Table.SelectRows( x, each ( [date] = List.Max(x[date]))) }}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"date", "a/c", "B1", "B2", "B3", "B4"}, {"date", "a/c", "B1", "B2", "B3", "B4"}) in #"Expanded Count"
