当前位置：文江博客话题详情

如何使用电源查询在同一列中进行增量求和？

发布于 2025-02-08 18:49:32 字数 271 浏览 2 评论 0原文

我有一个专栏[销售增长]，该列是五行（例如5个月）。当前行=前一行或上一行的1.05％。每行都会继续下降。

在Excel中非常简单，假设我在B2中我要做的就是B1*1.05。但是，在电源查询中，事实证明很难解决。我已经看到了各种具有双索引和索引-1等的解决方案，这非常适合运行总计，但在我的情况下却不适合运行。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不…忘初心 2025-02-15 18:49:32

很难准确地告诉您您要做什么。但是，如果您从某个基本数字开始，并且始终将每一行增加相同的％，那么它只是数学复合公式。

Result = Base * (1+Rate)^PeriodNumber

或用PowerQuery术语，

= Base * Number.Power(1+Rate,[Column_Containing_Period])

示例代码：

let
Starting=100,
Growth=.05,
Source =  Table.FromList({0..50}, Splitter.SplitByNothing(), {"Index"}, null, ExtraValues.Error ),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Starting*Number.Power(1+Growth,[Index]))
in #"Added Custom"

或

let
Starting=100,
Growth=.05,
Source =  List.Transform({0..50}, each Starting*Number.Power(1+Growth,_)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Result"}, null, ExtraValues.Error)
in #"Converted to Table"

It is hard to tell exactly what you are looking to do. But if you start with some base number and always increase each row by the same % then its just a math compounding formula.

Result = Base * (1+Rate)^PeriodNumber

or in powerquery terms,

= Base * Number.Power(1+Rate,[Column_Containing_Period])

sample code:

let
Starting=100,
Growth=.05,
Source =  Table.FromList({0..50}, Splitter.SplitByNothing(), {"Index"}, null, ExtraValues.Error ),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Starting*Number.Power(1+Growth,[Index]))
in #"Added Custom"

let
Starting=100,
Growth=.05,
Source =  List.Transform({0..50}, each Starting*Number.Power(1+Growth,_)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Result"}, null, ExtraValues.Error)
in #"Converted to Table"

回复收藏 0 原文

柳若烟 2025-02-15 18:49:32

查询1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1QGSegameBmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Factor", type number}}),
    BF = List.Buffer(#"Changed Type"[Factor]),
    Result = Table.FromColumns(
        {   
            #"Changed Type"[Factor], fx(BF)
        },
        {
            "Factor",
            "Result"
        }

    )



in
    Result

相关功能

(values as list) as list =>

let 

result = List.Generate(

    () => [result = 100, counter = 0],
    each [counter] < List.Count(values),
    each [result = [result] * values{[counter] + 1}, counter = [counter]+1 ],
    each [result]

)

in result

Query 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1QGSegameBmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Factor", type number}}),
    BF = List.Buffer(#"Changed Type"[Factor]),
    Result = Table.FromColumns(
        {   
            #"Changed Type"[Factor], fx(BF)
        },
        {
            "Factor",
            "Result"
        }

    )



in
    Result

Relevant Function

(values as list) as list =>

let 

result = List.Generate(

    () => [result = 100, counter = 0],
    each [counter] < List.Count(values),
    each [result = [result] * values{[counter] + 1}, counter = [counter]+1 ],
    each [result]

)

in result

回复收藏 0 原文

~没有更多了~