在查询中实施函数调用(分组运行总计) [英] Implement function call (grouped running totals) in query

问题描述

我有一个名为 fxGroupedRunningTotal (fxGRT)的函数和一个查询(总计).我想在Totals中调用fxGRT，以便获得一列来显示分组的运行总计.我只能通过导入Totals查询来测试fxGRT.

I have a function called fxGroupedRunningTotal (fxGRT) and a query (Totals). I want to call fxGRT within Totals, so that I get a column that shows the grouped running totals. I have only managed to test the fxGRT by importing the Totals query.

使用汇总并调用fxGRT(用于测试该功能的查询)的查询:

let
    Source = Totals,
    BufferedValues = List.Buffer(Source[PD]),
    BufferedMaterials = List.Buffer(Source[Material]),

    RT = Table.FromColumns(
    {
      Source[Material], Source[Date], Source[PD], 
      fxGroupedRunningTotal(BufferedValues, BufferedMaterials)
    },
    {
      "Material",
      "Date",
      "PD",
      "Running Total"
    })
in
    RT

fxGroupedRunningTotals

(values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    (
        ()=> [ GRT = values{0}, i = 0 ],

        each [i] < List.Count(values),

        each try if grouping{[i]} = grouping{[i] + 1}
                 then [GRT = [GRT] + values {[i] + 1}, i = [i] + 1]
                 else [GRT = values{[i] + 1}, i = [i] + 1]

            otherwise [i = [i] + 1]
    ,
        each [GRT]
    )
in
    GRTList

总计:

let
    Källa = Table.NestedJoin(Cohv,{"Prod MDate"},Resb,{"Del Mdate"},"Resb",JoinKind.FullOuter),
    #"Expanderad Resb" = Table.ExpandTableColumn(Källa, "Resb", {"Del Material", "Del Date", "Del Qty", "Del Mdate"}, {"Del Material", "Del Date", "Del Qty", "Del Mdate"}),
    #"PD Date" = Table.AddColumn(#"Expanderad Resb", "PD Date", each if [Prod Date] = null then [Del Date] else [Prod Date]),
    #"PD Material" = Table.AddColumn(#"PD Date", "PD Material", each if [Material Number] = null then [Del Material] else [Material Number]),
    #"PD Mdate" = Table.AddColumn(#"PD Material", "PD Mdate", each [PD Material] & "." & Date.ToText([PD Date])),
    #"Borttagna kolumner" = Table.RemoveColumns(#"PD Mdate",{"Prod Date", "Prod MDate", "Del Date", "Del Mdate"}),
    #"Ändrad typ1" = Table.TransformColumnTypes(#"Borttagna kolumner",{{"PD Date", type date}}),
    #"Ihopslagna frågor" = Table.NestedJoin(#"Ändrad typ1",{"PD Material"},Matmas,{"Material"},"Matmas",JoinKind.FullOuter),
    #"Expanderad Matmas" = Table.ExpandTableColumn(#"Ihopslagna frågor", "Matmas", {"Material", "Material Description", "MRP Controller", "Safety stock", "Minimum Lot Size", "In Stock"}, {"Material", "Material Description", "MRP Controller", "Safety stock", "Minimum Lot Size", "In Stock"}),
    #"Omdöpta kolumner" = Table.RenameColumns(#"Expanderad Matmas",{{"Material", "M Material"}}),
    #"Lägg till egen" = Table.AddColumn(#"Omdöpta kolumner", "Material", each if [PD Material] = null then [M Material]else [PD Material]),
    #"Borttagna kolumner1" = Table.RemoveColumns(#"Lägg till egen",{"Material Number", "Del Material", "PD Material", "M Material"}),
    #"Lägg till egen1" = Table.AddColumn(#"Borttagna kolumner1", "Date", each if [PD Date] = null then DateTime.LocalNow() else [PD Date]),
    #"Borttagna kolumner2" = Table.RemoveColumns(#"Lägg till egen1",{"PD Date"}),
    #"Lägg till egen2" = Table.AddColumn(#"Borttagna kolumner2", "Date in stock", each if [Date] = DateTime.Date(DateTime.LocalNow()) then [In Stock] else null),
    #"Borttagna kolumner3" = Table.RemoveColumns(#"Lägg till egen2",{"Date in stock"}),
    #"Ändrad typ" = Table.TransformColumnTypes(#"Borttagna kolumner3",{{"Date", type date}}),
    #"Ersatt värde" = Table.ReplaceValue(#"Ändrad typ",null,0,Replacer.ReplaceValue,{"Prod Qty"}),
    #"Ersatt värde1" = Table.ReplaceValue(#"Ersatt värde",null,0,Replacer.ReplaceValue,{"Del Qty"}),
    #"Ihopslagna frågor1" = Table.NestedJoin(#"Ersatt värde1",{"Date"},Dates,{"Date"},"Dates",JoinKind.RightOuter),
    #"Expanderad Dates" = Table.ExpandTableColumn(#"Ihopslagna frågor1", "Dates", {"Current Date"}, {"Current Date"}),
    #"Omdöpta kolumner1" = Table.RenameColumns(#"Expanderad Dates",{{"Date", "D Date"}}),
    Date = Table.AddColumn(#"Omdöpta kolumner1", "Date", each if [D Date] is null then DateTime.Date(DateTime.LocalNow()) else [D Date]),
    PD = Table.AddColumn(Date, "PD", each if [Current Date] = "Yes" then [In Stock]+[Prod Qty]-[Del Qty] else [Prod Qty]-[Del Qty])
in
    PD

那么，如何在总计"的新列中实现此功能?我的尝试一直失败.为了使这项工作有效，我必须参考汇总表吗?感觉很不对劲，因为这会使数据的工作量(?)翻倍.我希望尽快.

So how do I implement this function into a new column in my Totals? My attempts keep failing. Do I have to make a reference to Totals in order to make this work? It feels so wrong, since that would double the work load (?) with the data. I would like it to be as quick as possible.

推荐答案

您可以将上一步作为表格引用.这样您的查询就可以写了

You can reference the previous step as a table. Thus your query can be written

let
    [...all your previous steps...]
    PD = Table.AddColumn(Date, "PD", each if [Current Date] = "Yes" then [In Stock]+[Prod Qty]-[Del Qty] else [Prod Qty]-[Del Qty]),
    RT =
        Table.FromColumns(
            List.Combine({Table.ToColumns(PD), {fxGroupedRunningTotals(PD[Material], PD[PD])}}),
            List.Combine({Table.ColumnNames(PD), {"Running Total"}})
        )
in
    RT

这会将表转换为列列表，添加新的运行总计列(从上一步 PD 调用在表的特定列上定义的函数)，然后用类似的方法将这些列粘合在一起，以保留列名称并添加一个新列.

This converts the table to a list of columns, adds on the new running total column (calling the function you've defined on specific columns of the table from the previous step PD), and then glues those columns back together with a similar method to preserve the column names and add a new one.

这篇关于在查询中实施函数调用(分组运行总计)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！