How to aggregate data collected at the different time into the same date in Power Pivot

[Anonymous]

I have data collected at the different time of the day and would like to have a Pivot Table where it has date as columns and show sum of the data for each day as value.

Example of data collected:

Category	Date	#Object
A	5/4/2020 10:21:01 PM	9
A	5/4/2020 10:23:00 PM	7
A	5/2/2020 10:21:00 PM	13
A	5/2/2020 10:23:00 PM	2
B	5/4/2020 10:24:01 PM	6
B	5/4/2020 10:25:00 PM	7
B	5/2/2020 11:21:00 PM	10
B	5/2/2020 11:23:00 PM	34

Pivot table that I want:

	5/2/2020	5/4/2020
A	15	16
B	44	13

Right now when I pivot the data into Pivot Table in Excel, it doesn't recognize  the date only format that I already changed in Power Pivot. So instead of creating date column in Pivot table and aggregate data of the same day, it creates datetime columns so I would have multiple columns for each date. Please advice on how to sum them up and display just one column per day.

[AllisonKennedy · Accepted solution]

@Anonymous just to confirm you're using Excel and not Power BI? 

Data format in Power Pivot is helpful, but the real key is to get the date type correct. 

See my image below for two pivots based on the same table: 

 The one on top has the Data TYPE set to DATE in Power QUERY. 

The one on bottom has the Data type set to Date Time in Power QUERY.

Regardless of the data TYPE, you can still FORMAT either one as DATE, so the one on bottom would look like: 

Are you currently using Power QUERY to transform and clean the data? If not, do it! 🙂 It will help save you lots of time. In Power Query you can set the data type to DATE (or if you still need the time add a column with just the Date). 

 See here for info on Power Query (Get and Transform)

https://support.office.com/en-us/article/get-transform-in-excel-881c63c6-37c5-4ca2-b616-59e18d75b4de

You could do similar in Power Pivot, but I recommend doing it in Power Query as it is more powerful for cleaning data and when used it is where you must change data type.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[v-yiruan-msft · Accepted solution]

Hi @Anonymous,

You can create a calculated column to format the date field to day, then create a matrix visual and drag the related fields and new created calculated fields onto matrix (Rows: Category   Columns: new calculated column  Values: #Objects).

NewFDate = FORMAT('Objects'[Date],"MM/DD/YYYY")

Best Regards

Rena

[Anonymous]

Thank you @v-yiruan-msft ! your solution works just as well. 

[AllisonKennedy · Accepted solution]

@Anonymous just to confirm you're using Excel and not Power BI? 

Data format in Power Pivot is helpful, but the real key is to get the date type correct. 

See my image below for two pivots based on the same table: 

 The one on top has the Data TYPE set to DATE in Power QUERY. 

The one on bottom has the Data type set to Date Time in Power QUERY.

Regardless of the data TYPE, you can still FORMAT either one as DATE, so the one on bottom would look like: 

Are you currently using Power QUERY to transform and clean the data? If not, do it! 🙂 It will help save you lots of time. In Power Query you can set the data type to DATE (or if you still need the time add a column with just the Date). 

 See here for info on Power Query (Get and Transform)

https://support.office.com/en-us/article/get-transform-in-excel-881c63c6-37c5-4ca2-b616-59e18d75b4de

You could do similar in Power Pivot, but I recommend doing it in Power Query as it is more powerful for cleaning data and when used it is where you must change data type.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Thank you for the explanation! I'm very new to Power Pivot and Power BI so that is very helpful.