Forum Discussion

Copper Contributor

Feb 22, 2023

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, ...

excel

Formulas and Functions

Detlef_Lewin
Feb 22, 2023
AlexC616
Insert a pivot table.
"Fruit" in rows area.
"Date" twice in values area. Change the aggregation to Min and to Max.

AlexC616

Copper Contributor

Feb 22, 2023

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=true

Detlef_Lewin

Silver Contributor

Feb 22, 2023

AlexC616

That 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.