Forum Discussion
alexsandro
4 years agoFrequent Visitor
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...
- 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".
alexsandro
4 years agoFrequent Visitor
unfortunately it didn't work as I need to iterate over the same previous calculation and not over the previous price
lbendlin
Super User
4 years agolet
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".
- alexsandro4 years agoFrequent Visitor
IT WAS PERFECT, THANK YOU SO MUCH...
now I'm going to try to replicate this for several codes
you are very good at it, super professional