Forum Discussion
Solved
Finding the first & last entry for each category in a large dataset
Hello, I am working on a project where I have hundreds of tables with tens of thousands of entries. In each table, I have categories for multiple items and entry dates for each. For example, ...
Insert a pivot table.
"Fruit" in rows area.
"Date" twice in values area. Change the aggregation to Min and to Max.
Insert a pivot table.
"Fruit" in rows area.
"Date" twice in values area. Change the aggregation to Min and to Max.
- I have one more question. Could you please have a look over this secondary type of table I am dealing with? I have to get to the same result, but the data is arranged differently.
https://docs.google.com/spreadsheets/d/1RiuZsF2WGZPKJVzyKTwMSedssWsVcvFq/edit?usp=sharing&ouid=101102583844260137674&rtpof=true&sd=trueThat is a crosstabular table. You have to unpivot it. I suggest unpivot with Power Query.
You also have to change the values in column B into real dates.
Something like this:
let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribut", "Wert"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Wert] = "kg")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Wert"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribut", "Fruit"}}) in #"Renamed Columns"Then load the data into a pivot table on the worksheet.
- Detlef_Lewin
Oh my God it works. I didn't know you can put "Date" twice in values area. What a relief. Thank you so much!
