Forum Discussion

alexsandro's avatar
alexsandro
Frequent Visitor
4 years ago
Solved

replicate spreadsheet calculation

I'm trying to publish this formula of the spreadsheet in the power bi, but without success.   link of spreadsheet below: https://docs.google.com/spreadsheets/d/11BhRKjWPtPdeFuFvZSlBm0mesjtpG...
  • lbendlin's avatar
    lbendlin
    4 years ago
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddFJDoMwDAXQu7COhOfhLIht73+EEtNGJdAFKHJ4+Y7ZtgWXVs8LoK9oJV4JqJd6gc4yYEtN+l3ubVvogg9I3VaV9HjriaXVeQjU7NgO9dI8ax7aor6fOLfemScWl5nHl5NitX7TbvrJ1gkzDmz+0Dk3IfbwKG0XLevAjB3zHSe6FfWJ6qBSPd2DHVIgtHRMmgY3tLr5+F02RiYcXDr/ajd7HjgTKJzZCH95VPhDtmAq57Lvbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code_operation = _t, branch = _t, code = _t, date = _t, #"amount day" = _t, Amount = _t, price = _t, #"replicate this formula" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"amount day", Int64.Type}, {"Amount", Int64.Type}, {"code_operation", Int64.Type}, {"date", type date}}),
        #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"price", type number}}, "de-DE"),
        #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"code_operation", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 then [price] else List.Accumulate({1..[Index]},#"Added Index"{0}[price],(state,current)=>(#"Added Index"{current}[amount day]*state+#"Added Index"{current}[Amount]*#"Added Index"{current}[price])/(#"Added Index"{current}[amount day]+#"Added Index"{current}[Amount])))
    in
        #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".