topic Re: sum value from the time frame in Power Query

sum value from the time frame

Anonymous — Fri, 22 Jul 2022 08:05:21 GMT

I have two set data one is start date and end date, second data need to sum between the start date and end date.

First Data

Start date	End Date	Value
1/6/2022	24/6/2022	1080
25/6/2022	15/7/2022	900

Second Data

Date	Value
1/6/2022	100
2/6/2022	200
24/6/2022	150
28/6/2022	175
3/7/2022	181

I want to sum the data 2 in the time frame in data 1 and compare the both result. How should I do or what measure should I put?

Kind regards,

Kim

Re: sum value from the time frame

Vijay_A_Verma — Fri, 22 Jul 2022 10:11:32 GMT

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

Sample code for Table2

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcq5DQAgDAPAXVwTyTHvLij7rwHpEO3p9oYoGYfRUeAkojyoi/pQLWf/cCXO/uA01sTliDg=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}) in #"Changed Type"

Sample Code for Table1

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNM1MFTSgXOMTIAcQwMLA6VYHYQSI1O4EnNdQxDH0gCoIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Table2 Value", each List.Sum(Table.SelectRows(Table2, (x)=> x[Date]>=[Start Date] and x[Date]<=[End Date])[Value])) in #"Added Custom"

Re: sum value from the time frame

Anonymous — Tue, 26 Jul 2022 10:05:15 GMT

Hi, for the code below, how do i change it to my own data list, like adding more value inside.

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcq5DQAgDAPAXVwTyTHvLij7rwHpEO3p9oYoGYfRUeAkojyoi/pQLWf/cCXO/uA01sTliDg="

Thanks

Re: sum value from the time frame

Anonymous — Tue, 26 Jul 2022 11:04:20 GMT

let
Source = Excel.Workbook(File.Contents("xxx"), null, true),
IrishWaterBill_Table = Source{[Item="zzz",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(IrishWaterBill_Table,{{"Billing Period Start ", type date}, {"Billing Period End ", type date}, {"Days", Int64.Type}, {"Volume - m3", type number}, {"Water standing charge rate", type number}, {"Water supplied rate", type number}, {"Wastewater standing charge", type number}, {"Wastewater removed rate", type number}, {"Water Standing charge (Days*€1.36)", type number}, {"Water supplied ", type number}, {"Wastewter standing charge (Days*€1.4445)", type number}, {"Wastewater removed ", type number}, {"Total water service charge ", type number}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Billing Period Start ", "Billing Period End ", "Volume - m3", "Total water service charge "}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Table2 Value", each List.Sum(Table.SelectRows(WaterConsumption,(x)=> x[Date]>=[#"Billing Period Start "] and x[Date]<=[#"Billing Period End "])[MainIncomerWater])
in
#"Added Custom"