Sums row data / substrate and new table

Question

Hi,&nbsp;I have two set of data&nbsp;Data1 : Total used&nbsp;TimestampValue5/6/22505/6/22507/6/22607/6/22707/6/2280&nbsp;Data 2: Each streamTimestampStreamValue5/6/221105/6/221207/6/221237/6/221207/6/221505/6/222155/6/222157/6/222157/6/22267/6/22220&nbsp;The output table should be Stream 3. Value = total -stream 1-stream 2Timestamp&nbsp;Value5/6/22407/6/2276&nbsp;Thanks for your help. Really appreciate it.&nbsp;

jbwtp · Accepted Answer

Hi&nbsp;Anonymous,&nbsp;Something like this:let
    Streams = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3MNM3MjAyUtJRMgRhA6VYHUxhI4iwObqwMXZh7KpNMc0GS5niEzYnTtgMqyjIIbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Stream = _t, Value = _t]),
    Totals = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3MNM3MjAyUtJRMjVQitXBKmSOJGSGKWSOKWQBFIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Value = _t]),
    ReverseSignonStreams = Table.TransformColumns(Streams, {"Value", each Number.From(_) * -1}),
    Combine = Table.Combine({Totals, ReverseSignonStreams}),
    #"Changed Type" = Table.TransformColumnTypes(Combine,{{"Value", type number}}),
    Output = Table.Group(#"Changed Type", {"Timestamp"}, {{"Value", each List.Sum([Value]), type nullable number}})
in
    Output&nbsp;Kind regards,John

Forum Discussion

Sums row data / substrate and new table

2 Replies

Related content

table not summing

Filtering a Sum

Running sum

Improper Sum with slicer

SUM

Timestamp	Stream	Value
5/6/22	1	10
5/6/22	1	20
7/6/22	1	23
7/6/22	1	20
7/6/22	1	50
5/6/22	2	15
5/6/22	2	15
7/6/22	2	15
7/6/22	2	6
7/6/22	2	20