CODEWITHSUNDEEP
functionpowerquerym
TAKL
TAKL

Reputation: 339

Implement function call (grouped running totals) in query

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.

Query that uses Totals and calls fxGRT (built for testing the function):

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

Totals:

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.

Upvotes: 3

Views: 302

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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

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.

Upvotes: 2

Related Questions