Sums row data / substrate and new table

[Kim_Sky]

Hi, 

I have two set of data 

Data1 : Total used 

Timestamp	Value
5/6/22	50
5/6/22	50
7/6/22	60
7/6/22	70
7/6/22	80

Data 2: Each stream

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

The output table should be Stream 3. Value = total -stream 1-stream 2

Timestamp	Value
5/6/22	40
7/6/22	76

Thanks for your help. Really appreciate it. 

[jbwtp · Accepted solution]

Hi @Kim_Sky,

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

Kind regards,

John

[KT_Bsmart2gethe]

Hi @Kim_Sky ,

I created a blank query for the output. See below outcome and code:

Regards

KT