计算累计值

shufeabb

在query里算出累计值.zip (7.61 KB, 下载次数: 20)

2023-7-24 14:44 上传

点击文件名下载附件

onlylike

let
    源 = Excel.Workbook(File.Contents("C:\Users\Downloads\在query里算出累计值.xlsx"), null, true),
    Sheet1_Sheet = 源{[Item="Sheet1",Kind="Sheet"]}[Data],
    提升的标题 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    分组 = Table.Group(提升的标题,"Part Number",{"累计值",each List.Sum([Sum])}),
    合并 = Table.ExpandTableColumn(Table.NestedJoin(提升的标题, {"Part Number"}, 分组, {"Part Number"}, "分组", JoinKind.LeftOuter),"分组",{"累计值"}),
    占比 = Table.TransformColumnTypes(Table.AddColumn(合并, "占比", each [Sum]/[累计值]),{{"占比", Percentage.Type}})
in
    占比

xbq

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组的行 = Table.Group(源, {"Part Number"}, {{"MODDAITY", each _, type table [Part Number=text, Modality=text, Sum=number]}, {"合计", each List.Sum([Sum]), type number}}),
    #"展开的“MODDAITY”" = Table.ExpandTableColumn(分组的行, "MODDAITY", {"Modality", "Sum"}, {"Modality", "Sum"}),
    已添加自定义 = Table.AddColumn(#"展开的“MODDAITY”", "自定义", each [Sum]/[合计]),
    更改的类型 = Table.TransformColumnTypes(已添加自定义,{{"自定义", Percentage.Type}})
in
    更改的类型

小小道童

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"Part Number", type text}, {"Modality", type text}, {"Sum", type number}}),
    分组的行 =
        Table.Group(
            更改的类型, {"Part Number"},
            {
                {
                "计数",
                 each [a=Table.AddColumn(_,"汇总",(x)=>List.Sum(_[Sum])),b=Table.AddColumn(a,"百分比",each [Sum]/[汇总])][b]
                }
            }
        ),
    自定义1 = Table.Combine(分组的行[计数])
in
    自定义1

hbzql

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    累计值 = Table.AddColumn(源,"累计值",each Table.Group(源,"Part Number",{"分组",(x)=>List.Sum(x[Sum])}){[Part Number=[Part Number]]}[分组]),
    占比 = Table.AddColumn(累计值,"占比",each  Percentage.From([Sum]/[累计值])),
    更改的类型 = Table.TransformColumnTypes(占比,{{"占比", Percentage.Type}})
in
    更改的类型

lisongmei

let
    源 = Excel.CurrentWorkbook(){[Name="表2"]}[Content],
    自定义1 = 源,
    a = Table.ToRecords (Table.Group( 自定义1,"Part Number",{"Number",each List.Sum([Sum])})),
    total = Table.AddColumn( 自定义1,"汇总",each a{List.PositionOf(a,_,0,each _[Part Number])}[Number]),
    per = Table.AddColumn(total, "占比", each Number.ToText([Sum]/[汇总],"0.00%"))
in
    per