Forum Discussion
Calculated column for duplicate timestamps based on a scaled reference row
I currently have a dataset which contains Generation and Performance data for unique Postcodes separated into half-hour timesteps spanning a two year period. I have Generation data for all Postcodes itemised as '61' and am looking to use this information to calculate Generation based on Performance values for the other Postcodes.
The data set looks like this:
I am trying to calculate the Generation for Postcodes 60, 62, 63, 64... at each unique timestep using the formula:
Generation (for 62) = Generation (for 61) x Performance (62)/Performance(61)
i.e. Generation (for 62) = 10 x (23/70) = 3.29
Giving me a dataset which should look like this:
Is it possible to achieve this? Any help would be greatly appreciated!
Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY9BDoAwCAS/Yjg3KSwWjV9p+v9vaIupiaEXDgwsQ61kTIkkI4OBTezi3kAv1NLDJeLH2GKfQJigM0Ejfn4X9ohbmfxvqO/xlaHOD8RiQ08oS0NPKEtD38fg7QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postcode = _t, Timestamp = _t, Performance = _t, Generation = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Postcode", Int64.Type}, {"Timestamp", type datetime}, {"Performance", Int64.Type}, {"Generation", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Postcode] = 61)), GoBack = #"Changed Type", #"Added Custom" = Table.AddColumn(GoBack, "TempGeneration", each #"Filtered Rows"{[Timestamp=[Timestamp]]}[Generation]), #"Added Custom2" = Table.AddColumn(#"Added Custom", "TempPerformance", each #"Filtered Rows"{[Timestamp=[Timestamp]]}[Performance]), #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom", each if [Generation]=null then [TempGeneration]*[Performance]/[TempPerformance] else [Generation]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Generation", "TempGeneration", "TempPerformance"}), #"Rounded Off" = Table.TransformColumns(#"Removed Columns",{{"Custom", each Number.Round(_, 2), type number}}), #"Renamed Columns" = Table.RenameColumns(#"Rounded Off",{{"Custom", "Generation"}}) in #"Renamed Columns"
1 Reply
Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY9BDoAwCAS/Yjg3KSwWjV9p+v9vaIupiaEXDgwsQ61kTIkkI4OBTezi3kAv1NLDJeLH2GKfQJigM0Ejfn4X9ohbmfxvqO/xlaHOD8RiQ08oS0NPKEtD38fg7QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postcode = _t, Timestamp = _t, Performance = _t, Generation = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Postcode", Int64.Type}, {"Timestamp", type datetime}, {"Performance", Int64.Type}, {"Generation", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Postcode] = 61)), GoBack = #"Changed Type", #"Added Custom" = Table.AddColumn(GoBack, "TempGeneration", each #"Filtered Rows"{[Timestamp=[Timestamp]]}[Generation]), #"Added Custom2" = Table.AddColumn(#"Added Custom", "TempPerformance", each #"Filtered Rows"{[Timestamp=[Timestamp]]}[Performance]), #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom", each if [Generation]=null then [TempGeneration]*[Performance]/[TempPerformance] else [Generation]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Generation", "TempGeneration", "TempPerformance"}), #"Rounded Off" = Table.TransformColumns(#"Removed Columns",{{"Custom", each Number.Round(_, 2), type number}}), #"Renamed Columns" = Table.RenameColumns(#"Rounded Off",{{"Custom", "Generation"}}) in #"Renamed Columns"