How to summarize a spreadsheet based on the time-of-day column

Question

I want to summarize a spreadsheet based on the time-of-day column.

Here's a link to the spreadsheet. https://www.dropbox.com/s/nepq76ylhatgbvy/electric%20power%20monitoring%20summarized%20by%20day.csv?dl=0

TIA for your help.
SweetTasha

Answer 1

ronski2
Thanks for your reply.

I've never used Power Query or Pivot but I'm willing to learn. Do you know of a resource that might be helpful for me to learn the basics about them?

Thanks.

Answer 2

I've been learning/'playing with' PowerQuery for about 2 years now. Yes, I can give you some links to get you started.

Do you mean PowerPivot, now called Data Manager or PivotTables? I'd suggest starting with PivotTables, save DataManager for later when you need to deal with more than one source data table in a PivotTable.

.

Intro Webinars about "Automation" built in to Excel

Since “Excel Tables” automation was added in Excel 2007 MS has been working on ‘programmerless programming”. Or “Advanced automation”. Since then MS has added other feature such as:

.  *  Excel Tables

.  *  PowerQuery

.  *  Data Model / PowerPivot

.  *  PivotTables

.  *  PivotCharts

These features all incorporate LOTS of “automation” that reduces the need for users to write “code” by hand. 
.

In the following link, session #2 is a good overview example of how these features can all be used together to create “Dashboards”. Dashboards are impressive “summary” output that would be effectively impossible for the “average” user to create by writing formulas “by hand”.
.

3 free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI
https://www.myonlinetraininghub.com/excel-webinars
.
#2 Dashboards Using Power Query & Power Pivot Excel 2010  - 2019 and Office 365In this webinar I'm going to show you how to use Power Query to get data from multiple sources, filter and clean it, send it to Power Pivot and create relationships between data tables. Then I'll mash it up in PivotTables to create this interactive Excel Dashboard.
.

You asked for it ... <g>

Is this what you are looking for?

Use what you can, skip the rest.

.

A general tip, there are many organizations that give free (and paid) online webinars. They are a great learning resource. I included a link to one of them, myonlinetraininghub.com. There are others, like these 2 "user groups" I like to frequent.

Romainian User Group free Webinars, live and recordingshttps://www.meetup.com/romaniapug/

.
Vancouver PowerBI & Modern Excel User Group: Past EventsThis group hosts a couple of webinars every month. Some of the sessions are quite advanced, but they are often worth attending.
https://www.meetup.com/Vancouver-Power-BI-User-Group/events/past/
List and recordings of events for the last couple of years.
.

Here are links to 2 free seminars going on right now

John Acompora- Free Excel Training on The Modern Power Tools: Fri May 6 9am CDT - Mon May 16 9am CDT https://webinars.excelcampus.com/registration-blueprint
These Power Tools":
.  *  PowerQuery
.  *  PowerPivot / Data Manager
.  *  PowerBI
.  *  PivotTables (not in session)
.  *  PivotCharts (not in session)
.

This one will be over your head, but you may want to try attending some of the sessions to get a preview of some "advanced" techniques, as well as some basic ones you will be able to use.

Free Enterprise DNA "Reporting & Data Visualization" Summit - May 23-27

Enterprise DNA is offering another free, 5 day summit. This time it is for "Reporting & Data Visualization Summit"

.

Registration page: https://enterprisedna.co/reporting-and-data-visualization-summit-may-2022-registration/ Up to 7, 1hr, sessions each of the 5 days.

<snip>

DISCUSSIONS
Presented by Power BI, Power Platform & Data Visualization Experts from all around the world.

.

.

A high level learning program:

Step 1. Start using "Excel Tables". Very simple to apply, enables a bunch of automation

Step 2. Import data from Excel Table (or any other source) to PowerQuery to do "data cleaning"

Step 3. Use data output from PowerQuery in Excel PivotTables / PivotCharts

Step 4. Start using Data Manager to "join" data from multiple tables, start using "Date Tables" to enable additional date "automation" features.

.

#1 Learn Excel Tables

Overview of Excel tables ****
https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

Video: Create and format an Excel table ****

Total the data in an Excel table ****

Format an Excel table ****

Resize a table by adding or removing rows and columns****

Filter data in a range or table****

Convert a table to a range****

Excel table compatibility issues.

#2 Some introductory articles about PQ.

Change Your Life withExcel PQ
https://www.xelplus.com/excel-power-query-course-preview/
https://www.youtube.com/watch?v=6lBqYInBldk&list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-  9min
.
What if someone told you that there is a tool out there that can work magic on your data?
What if they said it can perform some of the most amazing data transformations you’ve ever seen?
What if they said you could reduce hours, if not days’ worth of work to mere minutes or seconds?
What if they said you can do this without writing a single formula or a single line of VBA code?
Interested?
Now, what if they told you that you already own this tool?
Let’s talk about Power Query.
.

What is Power Query?
https://powerquery.microsoft.com/en-us/
Microsoft’s Data Connectivity and Data Preparation technology that lets you seamlessly access data stored in hundreds of sources and reshape it to fit your needs—all with an easy to use, engaging, no-code experience.
.

What is PowerQuery-
https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query
Power Query is a data transformation and data preparation engine. Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Because the engine is available in many products and services, the destination where the data will be stored depends on where Power Query was used. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.
.

About Power Query in Excel-
https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a
With Power Query (known as Get & Transform in Excel), you can
.  ·  Connect    Make connections to data in the cloud, on a service, or locally
.  ·  Transform    Shape data to meet your needs, while the original source remains unchanged. for example remove a column, change a data type, or merge tables, in ways that meet your needs.
.  ·  Combine    Integrate data from multiple sources to get a unique view into the data
.  ·  Load   Complete your query and load it into a worksheet or Data Model and periodically refresh it.
Power Query is available in Excel for Windows, Excel for Mac and Excel for the Web. 
.

What is PowerQuery- https://corporatefinanceinstitute.com/resources/excel/study/power-query/
Power Query is a tool in Microsoft Excel that simplifies the process of importing data from different source files and sorting them into an Excel sheet in the most convenient and usable format.
It also eliminates the need to execute the same filtering techniques to transform the same data set at different points in time; the user only needs to set up a query, that is, the rules for sorting, once, and refresh the query every time the action is to be repeated.
.

How Power Query Will Change the Way You Use Excel- https://www.xelplus.com/excel-power-query-course-preview/
https://www.youtube.com/watch?v=6lBqYInBldk 9minIt can be argued that Power Query (also known as “Get & Transform”) is the single greatest feature in Excel.  Power Query can be used to help solve almost every Excel problem you encounter.
Can you think of any other feature in Excel that can make that claim?  Probably not.
One of the greatest selling points of Power Query is that the same problem can be solved with many different approaches.  It all comes down to your experience and creativity.
The more experience you gain, the more efficient your strategies become.  The more creatively you think, the greater the problems you can solve with ease.
.  *  Importing Data from Different Sources
.  *  Transforming and Cleaning Data
.  *  Load Your Data, create relationships
.  *  Power Query’s Best Kept Secret: With just a small investment in time, you can begin producing substantial results that will make any casual observer think you worked weeks to produce.
.

2020 10 14- How to easily automate boring Excel tasks with Power Query!
https://www.youtube.com/watch?v=L4BuUzccLpo       17min
Power Query can automate the boring and laborious tasks of getting and cleaning data, reducing time spent on these tasks down to the click of a button! Get the files for this lesson here: https://www.myonlinetraininghub.com/i...
00:00 Introduction
00:27 Where to find Power Query
01:14 Why should you use Power Query
03:44 Power Query example - get files from a folder
06:04 Power Query Editor - cleaning the data
13:37 Close & Load Options
14:27 Building PivotTables from Power Query data
15:37 Updating with new data with one click!
.

Automate Data Tasks in Excel & Power BIhttps://www.excelcampus.com/power-tools/power-query-overview/  (article)https://www.youtube.com/watch?v=vq9AgAtSvQg 16min (YouTube)
Learn how to use Power Query, which is a data automation tool that allows us to import, transform, and cleanse data in Excel or Power BI. In the video I share an overview of the Power Query workflow and then go into a hands on demo. You will learn how to combine multiple CSV files, clean up the data, and automate the entire process for when you get new/updated data.
This video is an introduction for beginners. If you have tried to use Power Query, but are confused by the process or user interface, then this training is for you.
Power Query is a tool that can save you a ton of time with your work by automating common data processes like:
.  ✔ Remove columns, rows, blanks
.  ✔ Convert data types - text, numbers, dates
.  ✔ Split or merge columns
.  ✔ Sort & filter columns
.  ✔ Add calculated columns
.  ✔ Aggregate or summarize data
.  ✔ Find & replace text
.  ✔ Unpivot data to use for pivot tables
.  ✔ Merge (join) data tables together
.  ✔ Combine (append) data tables, sheets, and workbooks
.

Ultimate Beginners Guide to PBI (free)https://portal.enterprisedna.co/courses/enrolled/157239
This is a free course from Enterprise DNA. The first 3-4 sections are a good video introduction to PowerQuery and Data Modelling .
.

Beyond "basic" PowerQuery

Joins – Do “VLookUp” in PowerQuery

Tip #1: It took me a while (too Long) to realize that a “Join” or Query Merge in PowerQuery (and DataManager) is the equivalent of doing any of the “lookup” functions (XLookUp, VLookUp, HLookUp) Excel. Matching, “joining”, data from 2 or more sources gives you much more flexibility later when doing data reporting in PivotTables
.

Join types in Power Query – SEE how it works in GIFs (short demo)https://ssbi-blog.de/blog/technical-topics-english/join-types-in-power-query-see-how-it-works/
The GIFS on this page quickly demonstrates the effects of the various join/merge types.
Merge queries is one way to combine queries (or even different tables within the same query) in Power Query/M.
.

Types of merging of queries in Power Query or in Power BI – Very SHORT Demo of resultshttps://exceltown.com/en/tutorials/power-bi/power-query-get-and-transform/types-of-merging-of-queries-in-power-query-or-in-power-bi/ 
This article describes the differences between types of merging of queries. We are talking about merging of queries by some key (ID). This is not about the appending of queries.
We will use these two tables, that are supposed to be merged. The values from the orange one should be assigned to rows in the blue one, when the ID is key. 
.

Merge queries (Power Query) (MS article)https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9
When you merge, you typically join two queries that are either within Excel or from an external data source. In addition, the Merge feature has an intuitive user interface to help you easily join two related tables. For an example of merging total sales from an order details query into a products table, see the Learn to combine multiple data sources tutorial.
.

Tip #2: Unpivot data to create PivotTables

Often we get data in “grid” or “pivot” format that we want to use in a PivotTable. But if you use this data in a PivotTable you get all sorts of flakey results. You just can’t get the results you want. The trick is learning to recognize incoming data in this format and knowing that you will have to use PowerQuery to “unpivot” it. The general format is a grid of repeating categories both in rows and columns. For example, rows contain information like employee names, or equipment, cities etc. and across the columns you get dates (months, years) or repeating data like phone numbers (home, work, cell). This is not “excel friendly”. The “unpivotted” data would be something like employee name (or part name), date, quantity.
.

Power Query Unpivot - fix 4 common data layoutshttps://www.myonlinetraininghub.com/power-query-unpivot-scenarios  (article)
https://www.youtube.com/watch?v=-IMqkg35adA 19min
How to use Excel Power Query Unpivot to fix 4 common data layouts. Click here to download the workbook with step by step instructions: https://www.myonlinetraininghub.com/w...
Example 1: Sales info by person by year with row totals Example 2: Partial tabular, repeating columns monthly hours and costs Example 3: column headings over 2 rows Example 4: Stacks of repeating rows .

Partially Stack Data In Excel With Power Queryhttps://www.youtube.com/watch?v=cZLd3mX1YHU&t=25s    5min28
Starting with “Pivoted” data, want to partially Stack / unpivot
.

#3 PivotTables

Overview of PivotTables and PivotChartshttps://support.microsoft.com/en-us/office/overview-of-pivottables-and-pivotcharts-527c8fa3-02c0-445a-a2db-7794676bce96
You can use a PivotTable to summarize, analyze, explore, and present summary data. PivotCharts complement PivotTables by adding visualizations to the summary data in a PivotTable, and allow you to easily see comparisons, patterns, and trends. Both PivotTables and PivotCharts enable you to make informed decisions about critical data in your enterprise. You can also connect to external data sources such as SQL Server tables, SQL Server Analysis Services cubes, Azure Marketplace, Office Data Connection (.odc) files, XML files, Access databases, and text files to create PivotTables, or use existing PivotTables to create new tables.
.

Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)https://www.youtube.com/watch?v=9NUjHBNWe9M    14:47  Excel Campus Jon
In this video series you will learn how to create an interactive dashboard using Pivot Tables and Pivot Charts.
Don't worry if you have never created a Pivot Table before, I cover the basics of formatting your source data and creating your first Pivot Table as well. You will also get to see an add-in I developed named PivotPal that makes it easier to work with some aspects of Pivot Tables.
.

101 Advanced Pivot Table Tips And Tricks You Need To Knowhttps://www.howtoexcel.org/pivot-tables/pivot-table-tips-and-tricks/
Pivot tables are awesome! They’re one of Excel’s most powerful features, they allow you to quickly summarize large amounts of data in a matter of seconds. This collection of awesome tips and tricks will help you master pivot tables and become a data ninja!
Great collection of quick tips for commonly asked questions.
.

Design the layout and format of a PivotTablehttps://support.microsoft.com/en-us/office/design-the-layout-and-format-of-a-pivottable-a9600265-95bf-4900-868e-641133c05a80
After creating a PivotTable and adding the fields that you want to analyze, you may want to enhance the report layout and format to make the data easier to read and scan for details. To change the layout of a PivotTable, you can change the PivotTable form and the way that fields, columns, rows, subtotals, empty cells and lines are displayed. To change the format of the PivotTable, you can apply a predefined style, banded rows, and conditional formatting.
.

Secrets to Building Excel Dashboards based on Non-financial Data     Mynda Treacy           2021 05 20
https://www.myonlinetraininghub.com/non-financial-excel-dashboards
Dashboards give a visual snapshot of your business performance. They typically show data spread over time and grouped by category. The most common dashboard examples are based on financial data, so in this video tutorial I’ll show you how to build non-financial Excel dashboards.
I’ll be including some techniques I haven’t demoed in any of my other dashboard tutorials so stay tuned to the end.
.

How to build Interactive Excel Dashboards that Update with ONE CLICK!       Mynda Treacy 2017 12 18
https://www.youtube.com/watch?v=K74_FNnlIF8
In this video you will learn how to create an interactive Excel dashboard from scratch using the built in Excel tools. No add-ins or VBA/Macros. Just plain Excel. Applies to Excel 2007 onward for Windows & Excel 2016 onward for Mac.
Chapters:00:00 Introduction, what to expect and keys to building dashboards fast - don't skip this if it's your first time watching ;-)
04:45 The Data
05:07 Building the PivotTables & Charts
28:00 Inserting Slicers
29:50 Manual Legend
33:54 Dynamic Labels
39:11 Link Slicers to Charts
42:26 Adding New Data
44:43 Color Formatting with Themes
45:45 Formatting Objects
48:16 Make it look less like Excel
49:26 Worksheet Protection
50:47 What's Next
.

#4 Data Manager

Where is Power Pivot?

https://support.microsoft.com/en-us/office/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b

**Note:**This article was last updated on 1/8/2019. Power Pivot availability will depend on your current version of Office. If you're a Microsoft 365 subscriber, make sure you have the latest updates installed.

.

Ultimate Beginners Guide to PBI (free)

https://portal.enterprisedna.co/courses/enrolled/157239

This is a free course from Enterprise DNA. The first 3-4 sections are a good video introduction to PowerQuery and Data Modelling .
. Create a Data Model in Excel https://support.microsoft.com/en-us/office/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b

A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables and PivotCharts. A Data Model is visualized as a collection of tables in a Field List, and most of the time, you’ll never even know it's there.

Before you can start working with the Data Model, you need to get some data. For that we'll use the Get & Transform (Power Query) experience, so you might want to take a step back and watch a video, or follow our learning guide on Get & Transform and Power Pivot.
. Create a memory-efficient Data Model using Excel and the Power Pivot add-in https://support.microsoft.com/en-us/office/create-a-memory-efficient-data-model-using-excel-and-the-power-pivot-add-in-951c73a9-21c4-46ab-9f5e-14a2833b6a70

In Excel 2013 or later, you can create data models containing millions of rows, and then perform powerful data analysis against these models. Data models can be created with or without the Power Pivot add-in to support any number of PivotTables, charts, and Power View visualizations in the same workbook.

.

Data Model / Power Pivot - Overview and Learning

https://support.microsoft.com/en-us/office/power-pivot-overview-and-learning-f9001958-7901-4caa-ad80-028a6d2432ed

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013

Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.

.

Create a relationship between tables in Excel

https://support.microsoft.com/en-us/office/create-a-relationship-between-tables-in-excel-fe1b6be7-1d85-4add-a629-8a3848820be3

Have you ever used VLOOKUP to bring a column from one table into another table? Now that Excel has a built-in Data Model, VLOOKUP is obsolete. You can create a relationship between two tables of data, based on matching data in each table. Then you can create Power View sheets and build PivotTables and other reports with fields from each table, even when the tables are from different sources. For example, if you have customer sales data, you might want to import and relate time intelligence data to analyze sales patterns by year and month.

.

Creating Linked "Fact" Tables - Monkey Shorts Episode 010

https://www.youtube.com/watch?v=F8n83IAAH9M

Mar 24, 2022             Skillwave Training

Ken looks at a simple Power Pivot trick in this video which helps:

-Prevent users from accidentally adding a "foreign key" to a PivotTable (resulting in cross filtering issues) Hide in PowerPivot

-Keep data organized

-Visibly identify tables with measures on them

.