Pseudo code

[PSRai]

Hi 

I have a pseudo code which I need to put into Power bi. As Im new to both I need some help with putting the code in Pbi

The energy reading im pulling out of the database vary from 2-6 seconds per minute. I need to fill in th egaps so that their are readings every minute to work out the KWH (kilo watts/hour)

Array  Readings[Num_Energy_Readings]

Array CorrectedReadings[Num_Energy_Readings]

Count =0

EnergyAdjust = 0

TimeOffset=Readings[0]              ‘ Set energy to 0 at the first time required to clear previous energy

CorrectedReadings[0]=Readings[0]-TimeOffset    ‘ Load first readings

For count=1 to count = Num_Energy_Readings

{

                If Readings[count] <  Readings[count-1]

                {

                                EnergyAdjust=EnergyAdjust + Readings[count-1]

                }

                CorrectedReadings[count]=Readings[count]+EnergyAdjust[]-TimeOffset

}

[Stachu]

that's not really how PowerBi works

what's your input, and what's your intended output?

please post it as tables, following the instructions here:

How to Get Your Question Answered Quickly 

[PSRai]

Hi

Inpu is coming as a direct query from Google Big Query, Output will be energy in seconds

I have the energy from Big query by every 2-5 seconds but in order to provide a more accurate KWH I need to plug in the missing seconds.

My aim is to take the difference between the last two readings and work out the average of the missing seconds for e.g.

Date/Time                                                Energy

15/07/2019 - 00:00:01                              0.09

15/07/2019 - 00:00:03                              1.10

 15/07/2019 - 00:00:05                             0.08

15/07/2019 - 00:00:07                              2.03

The output Im looking for is

Date/Time                                                Energy

15/07/2019 - 00:00:01                             0.09

15/07/2019 - 00:00:02                             0.60

15/07/2019 - 00:00:03                             1.10

15/07/2019 - 00:00:04                             0.59

15/07/2019 - 00:00:05                             0.08

15/07/2019 - 00:00:06                             1.06

15/07/2019 - 00:00:07                             2.03

[Stachu · Accepted solution]

this makes use of @ImkeF's previous row reference
https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

Add the code for the first function as a new query, name it ReferencePreviousRow

then you can apply this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVNzDXNzIwtFQwMLACIUMlHSUDPQNLpVgdbNLGQGlDPUMcsqYQzRY4pC2A0kZ6QDNiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date/Time" = _t, Energy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Energy", type number}}),
    PreviousDateTime_Add = ReferencePreviousRow(#"Changed Type", "Date/Time"),
    PreviousDateTime_Rename = Table.RenameColumns(PreviousDateTime_Add,{{"Previous Row", "PrevDateTime"}}),
    PreviousEnergy_Add = ReferencePreviousRow(PreviousDateTime_Rename, "Energy"),
    PreviousEnergy_Rename = Table.RenameColumns(PreviousEnergy_Add,{{"Previous Row", "PrevEnergy"}}),
    TimeDelta_Add = Table.AddColumn(PreviousEnergy_Rename, "TimeDelta", each [#"Date/Time"]-[PrevDateTime]),
    TimeDelta_Type = Table.TransformColumnTypes(TimeDelta_Add,{{"TimeDelta", type duration}}),
    TimeDelta_HandleNull = Table.ReplaceValue(TimeDelta_Type,null,#duration(0, 0, 0, 0),Replacer.ReplaceValue,{"TimeDelta"}),
    ListTimeDeltas = Table.AddColumn(TimeDelta_HandleNull, "ListTimeDelta", each List.Generate(()=>-[TimeDelta] + #duration(0,0,0,1), each _ <= #duration(0,0,0,0), each _ + #duration(0,0,0,1))),
    EnergyDecrement_Add = Table.AddColumn(ListTimeDeltas, "EnergyDecrement", each ([Energy]-[PrevEnergy])/(List.Count([ListTimeDelta]))),
    EnergyDecrement_HandleNull = Table.ReplaceValue(EnergyDecrement_Add,null,0,Replacer.ReplaceValue,{"EnergyDecrement"}),
    #"Added Custom1" = Table.AddColumn(EnergyDecrement_HandleNull, "ListEnergyDecrement", each List.Generate(() => -List.Count([ListTimeDelta])+1, each _ <= 0, each _ +1)),
    Lists_Zip = Table.AddColumn(#"Added Custom1", "Custom", each List.Zip({[ListTimeDelta], [ListEnergyDecrement]})),
    #"Removed Other Columns" = Table.SelectColumns(Lists_Zip,{"Date/Time", "Energy", "EnergyDecrement", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Extracted Values", {{"Custom", "00:00:00,0"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Errors", "Custom", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, true), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type duration}, {"Custom.2", type number}}),
    NewDateTime_Add = Table.AddColumn(#"Changed Type1", "NewDateTime", each [#"Date/Time"]+[Custom.1]),
    NewEnergy_Add = Table.AddColumn(NewDateTime_Add, "NewEnergy", each [Energy]+[EnergyDecrement]*[Custom.2]),
    FinalCleanup = Table.SelectColumns(NewEnergy_Add,{"NewDateTime", "NewEnergy"})
in
    FinalCleanup

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!