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, let's say I have a table where I have the entry dates for 4 types of fruit: apple, orange, papaya and kiwi. I can have thousands of Apple entries, starting all the way back in 2004, and ending in 2019, while for oranges, the entries can start in 2001 and end in 2021. I have organized the data using Pivot tables for it to show me the entries for each fruit category in chronological order. However, since my dataset has tens of thousands of entries, and I must do this for a lot of tables, I end up scrolling for hours to manually find the first and last entry for each item category, in each table.
I have created a spreadsheet where there is a small example of what I must do. I have been trying to create a formula that looks for each Apple/Papaya/Kiwi/Orange entry and returns the First (Min) and Last (Max) entries for each, with no success so far. (https://docs.google.com/spreadsheets/d/1NLPNfvuRA33a_9eJz3Naicu6bDPxlK8Z/edit?usp=sharing&ouid=101102583844260137674&rtpof=true&sd=true)
Thank you in advance!
Insert a pivot table.
"Fruit" in rows area.
"Date" twice in values area. Change the aggregation to Min and to Max.
4 Replies
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!
