Forum Discussion
rahul632soni
Helper I
Helper Ihow to get sum with specific data
I have this data available , now i want to calculate Header Hours which is Difference of Max and Min Engine HOurs , However it is working with Same level of Grouping as we have Engine Hours ...
rahul632soniHelper I
Helper IHey Below is the table with data which is availabe
| Header_Name | vin | Instance-Flag | ENG_HOURS |
| A | HAJSR22HKMG679922 | 1 | 202.1 |
| A | HAJSR22HKMG679922 | 1 | 202.2 |
| A | HAJSR22HKMG679922 | 1 | 202.3 |
| A | HAJSR22HKMG679922 | 1 | 202.4 |
| A | HAJSR22HKMG679922 | 1 | 208.7 |
| A | HAJSR22HKMG679922 | 1 | 208.8 |
| A | HAJSR22HKMG679922 | 1 | 208.9 |
| A | HAJSR22HKMG679922 | 1 | 209 |
| A | HAJW210HKMG679924 | 1 | 1153.9 |
| A | HAJW210HKMG679924 | 1 | 1154 |
| A | HAJW210HKMG679924 | 1 | 1154.1 |
| A | HAJW210HKMG679924 | 1 | 1154.2 |
| A | HAJW210HKMG679924 | 1 | 1154.3 |
| A | HAJW210HKMG679924 | 1 | 1234.2 |
| A | HAJW210HKMG679924 | 1 | 1234.3 |
| A | HAJW210HKMG679924 | 1 | 1234.4 |
| A | HAJW210HKMG679924 | 1 | 1234.5 |
| A | HAJW210HKMG679924 | 2 | 100 |
| A | HAJW210HKMG679924 | 2 | 100.1 |
| A | HAJW210HKMG679924 | 2 | 100.2 |
| A | HAJW210HKMG679924 | 2 | 100.3 |
| A | HAJW210HKMG679924 | 2 | 100.4 |
| A | HAJW210HKMG679924 | 2 | 199.7 |
| A | HAJW210HKMG679924 | 2 | 199.8 |
| A | HAJW210HKMG679924 | 2 | 199.9 |
| A | HAJW210HKMG679924 | 2 | 200 |
| B | HAJSR22HKMG679922 | 1 | 241 |
| B | HAJSR22HKMG679922 | 1 | 241.1 |
| B | HAJSR22HKMG679922 | 1 | 241.2 |
| B | HAJSR22HKMG679922 | 1 | 241.9 |
| B | HAJSR22HKMG679922 | 1 | 242 |
| B | HAJSR22HKMG679922 | 1 | 242.1 |
| B | HAJSR22HKMG679922 | 1 | 259.4 |
| B | HAJSR22HKMG679922 | 1 | 259.5 |
| B | HAJSR22HKMG679922 | 1 | 259.6 |
| B | HAJSR22HKMG679922 | 1 | 259.7 |
| B | HAJSR22HKMG679922 | 1 | 259.8 |
| B | HAJSR22HKMG679922 | 1 | 259.9 |
| B | HAJSR22HKMG679922 | 1 | 260 |
| B | HAJSR26HLMG679906 | 1 | 739.4 |
| B | HAJSR26HLMG679906 | 1 | 739.5 |
| B | HAJSR26HLMG679906 | 1 | 739.6 |
| B | HAJSR26HLMG679906 | 1 | 739.7 |
| B | HAJSR26HLMG679906 | 1 | 739.8 |
| B | HAJSR26HLMG679906 | 1 | 739.9 |
| B | HAJSR26HLMG679906 | 1 | 766.5 |
| B | HAJSR26HLMG679906 | 1 | 766.6 |
| B | HAJSR26HLMG679906 | 1 | 766.7 |
This Talks about that a Particular Header was attached to a VIn during which Engine Hours .
we need Total Engine Hours for Each Header , Engine Hours can be calculated using Difference of Max Engine Hours and Min Engine Hours For Each VIn and Each Instance for Each Header .
Below is the Output Required
| Header_Name | Header Hours |
| A | 187.5 |
| B | 46.3 |
Regrads
Rahul
lbendlin
Super User
Super Userlet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdK7DsMgDAXQf2GOEJinx3Rp1MfSDh2i/P9vlLwqhRK4Aws6suxrj6PoRSeG/vZ+EQ3359UHZqL0p9MjRVKLqWsrgpSBlAVUlAFSEVIMqIP5kFY/YzejtTMSYhZCx+jPGWHMtBkZqNrMwGrApDNzVbYsQSnENDLbVX3IXdVn3FV9xEUxH8/1VEVI1W+MlnNd07rUTtpqxEhMEaQYUEglQrpyvO2mpRykPKQCpCKkgLR8vmc/PFaj/GaC+c+hrPIcyirPoazyHMoqz6Gs8hwKynuk+6SA7pNK3U9f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header_Name = _t, vin = _t, #"Instance-Flag" = _t, ENG_HOURS = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ENG_HOURS", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Header_Name","vin","Instance-Flag"}, {{"Min Hours", each List.Min([ENG_HOURS]), type number}, {"Max Hours", each List.Max([ENG_HOURS]), type number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Header Hours", each [Max Hours]-[Min Hours]),
#"Grouped Rows1" = Table.Group(#"Added Custom", {"Header_Name"}, {{"Header Hours", each List.Sum([Header Hours]), type number}})
in
#"Grouped Rows1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".