cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Need Help in Calculating the Read total by serial no

Hi All,

I need help in find the total read for an entire year of each installation number.

Here is a sample data and the output which i am looking for.

 Number Serial Number Date Reason Read 9000002 1692360 6/13/2022 0:00 1 1932 9000002 1692360 5/12/2022 0:00 1 1872 9000002 1692360 5/9/2022 0:00 10 1863 9000002 1692360 4/11/2022 0:00 1 1671 9000002 1692360 3/11/2022 0:00 1 1230 9000002 1692360 2/10/2022 0:00 1 779 9000002 1692360 1/14/2022 0:00 1 499 9000002 1692360 12/13/2021 0:00 1 0 9000002 1692360 12/8/2021 0:00 21 0 9000002 1692424 12/7/2021 0:00 22 1086 9000002 1692424 11/12/2021 0:00 1 689 9000002 1692424 10/14/2021 0:00 1 350 9000002 1692424 9/14/2021 0:00 1 76 9000002 1692424 8/13/2021 0:00 1 11 9000002 1692424 8/12/2021 0:00 21 0 9000002 1573157 8/11/2021 0:00 22 14150 9000002 1573157 7/12/2021 0:00 1 14113 Output 9000002 1692360 6/13/2022 0:00 1 1932 9000002 1692360 8/13/2021 0:00 1 11 9000002 1692424 12/7/2021 0:00 22 1086 9000002 1692424 12/13/2021 0:00 1 0 9000002 1573157 8/11/2021 0:00 22 14150 9000002 1573157 7/12/2021 0:00 1 14113 Final Output Number total reads Formula 9000002 3044 =(E88-E89)+(E90-E91)+(E92-E93)
1 ACCEPTED SOLUTION
Super User

So for each serial number you want to find the difference between the oldest and newest readings, and then you want to sum these differences?

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdLhCoUgDAXgV7n4O3BnM6f3VaL3f42sJK64xQ2MqPPhNtu2UOm8OCwBubJkak85QiIT84e+dL7Auapw2BdbrBE8i6Jvoo7gupUsrkgRmPfICleIKVjIFRxBk1CtLkBEmkCqL4D7cPEr/Ipavgxx9vKJ053XMX99p5J9gn56Q0m5WE10Qb3tQcjql1UtoH5NxZoSrKN+8vzHmFaVtu48jDElzD08Rq0xNYL2z+4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, #"Serial Number" = _t, Date = _t, Reason = _t, Read = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Read", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Serial Number"}, {{"Rows", each _, type table [Number=nullable text, Serial Number=nullable text, Date=nullable datetime, Reason=nullable text, Read=nullable number]}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Delta", Int64.Type}})
in
List.Sum(#"Changed Type1"[Delta])``````

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".

2 REPLIES 2
Community Support

Hi @Anonymous

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Super User

So for each serial number you want to find the difference between the oldest and newest readings, and then you want to sum these differences?

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdLhCoUgDAXgV7n4O3BnM6f3VaL3f42sJK64xQ2MqPPhNtu2UOm8OCwBubJkak85QiIT84e+dL7Auapw2BdbrBE8i6Jvoo7gupUsrkgRmPfICleIKVjIFRxBk1CtLkBEmkCqL4D7cPEr/Ipavgxx9vKJ053XMX99p5J9gn56Q0m5WE10Qb3tQcjql1UtoH5NxZoSrKN+8vzHmFaVtu48jDElzD08Rq0xNYL2z+4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, #"Serial Number" = _t, Date = _t, Reason = _t, Read = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Read", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Serial Number"}, {{"Rows", each _, type table [Number=nullable text, Serial Number=nullable text, Date=nullable datetime, Reason=nullable text, Read=nullable number]}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Delta", Int64.Type}})
in
List.Sum(#"Changed Type1"[Delta])``````

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".

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors