Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
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 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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thank you for the explanation! I'm very new to Power Pivot and Power BI so that is very helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |