Forum Discussion
Solved
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 ...
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"
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalOpen 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"