Forum Discussion
sum value from the time frame
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"
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"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
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"