Summarize table

[CsJHUN]

Hi Gals and Guys,

let say i have this:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style><style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>date</th><th>stop reason1</th><th>Stop time 1</th><th>stop reason2</th><th>Stop time 2</th><th>stop reason3</th><th>Stop time 3</th><th>stop reason4</th><th>Stop time 4</th><th>stop reason5</th><th>Stop time 5</th></tr></thead><tbody>
<tr><td>6/2/2019</td><td>Thing24</td><td>55</td><td> </td><td> </td><td> </td><td> </td><td>Thing19</td><td>31</td><td> </td><td> </td></tr>
<tr><td>5/29/2019</td><td>Thing16</td><td>52</td><td> </td><td> </td><td>Thing21</td><td>1</td><td>Thing12</td><td>23</td><td>Thing19</td><td>41</td></tr>
<tr><td>5/24/2019</td><td>Thing1</td><td>16</td><td>Thing18</td><td>56</td><td>Thing12</td><td>53</td><td> </td><td> </td><td>Thing5</td><td>4</td></tr>
<tr><td>6/2/2019</td><td> </td><td> </td><td> </td><td> </td><td>Thing18</td><td>24</td><td> </td><td> </td><td>Thing18</td><td>3</td></tr>
<tr><td>5/24/2019</td><td> </td><td> </td><td> </td><td> </td><td>Thing1</td><td>29</td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>6/3/2019</td><td>Thing24</td><td>32</td><td>Thing21</td><td>16</td><td>Thing1</td><td>8</td><td>Thing16</td><td>25</td><td>Thing2</td><td>5</td></tr>
<tr><td>5/30/2019</td><td>Thing24</td><td>5</td><td>Thing22</td><td>1</td><td> </td><td> </td><td>Thing15</td><td>42</td><td> </td><td></td></tr>
</tbody></table>
i want to make the top 3 stop reason for each day with summarized time.

Any advice?

cheers

 

[sandy666]

something like this?

with PowerQuery and PivotTable

date	stop reason1	Stop time 1	stop reason2	Stop time 2	stop reason3	Stop time 3	stop reason4	Stop time 4	stop reason5	Stop time 5
02/06/2019​	Thing24	55​					Thing19	31​
29/05/2019​	Thing16	52​			Thing21	1​	Thing12	23​	Thing19	41​
24/05/2019​	Thing1	16​	Thing18	56​	Thing12	53​			Thing5	4​
02/06/2019​					Thing18	24​			Thing18	3​
24/05/2019​					Thing1	29​
03/06/2019​	Thing24	32​	Thing21	16​	Thing1	8​	Thing16	25​	Thing2	5​
30/05/2019​	Thing24	5​	Thing22	1​			Thing15	42​
stop time	(All)
Thing	(All)
date	stop reason	Sum of stop time value
24/05/2019​	stop reason1	129​
	stop reason2	129​
	stop reason3	158​
	stop reason5	129​
29/05/2019​	stop reason1	117​
	stop reason3	117​
	stop reason4	117​
	stop reason5	117​
30/05/2019​	stop reason1	48​
	stop reason2	48​
	stop reason4	48​
02/06/2019​	stop reason1	86​
	stop reason3	27​
	stop reason4	86​
	stop reason5	27​
03/06/2019​	stop reason1	86​
	stop reason2	86​
	stop reason3	86​
	stop reason4	86​
	stop reason5	86​

 

[CsJHUN]

Thx Sandy, really promising.
Looking for something more like this:
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

Date	Stop reason	Time
2019-02-06	Thing24	55
2019-02-06	Thing19	31
2019-02-06	Thing18	24
2019-03-06	Thing24	32
2019-03-06	Thing16	25
2019-03-06	Thing21	16
2019-05-24	Thing18	56
2019-05-24	Thing12	53
2019-05-24	Thing1	45
2019-05-29	Thing16	52
2019-05-29	Thing19	41
2019-05-29	Thing12	23
2019-05-30	Thing15	42
2019-05-30	Thing24	5
2019-05-30	Thing22	1

<tbody>
</tbody>

We only have powerpivot, if thats the same as power querry i still concenr it has to be enabled at the "end user" or its saved and carried with the file?
How you set up pivot to look like yours. Because if i move the reason fields to the row/column lablels its cascade them below each other.

 

[sandy666]

PowerQuery == Get&Transform

 

[sandy666]

on the ribbon: