Expression.Error: Access field by index in table

Hi Anonymous ,

You didn't provide an example of what your desired output looks like, so I've put together the table below which should give you everything you need to complete it as you need.

There's a number of ways that you could apportion the monthly budget into daily budget, each slightly more complicated than the last, so I've just given you a [monthsBetween] column and a [dateList] column that should be the basis for whichever method you choose.

Pete

let
    Source = Table.FromRecords(
        {
            [fromDate = "1.2.20", categoryName = "Offerte", monthlyBudget = 300, userName = "Max Meier"]
            ,[fromDate = "2.4.22", categoryName = "Offerte", monthlyBudget = 100, userName = "Max Meier"]
            ,[fromDate = "1.8.21", categoryName = "Offerte", monthlyBudget = 220, userName = "Max Meier"]
            ,[fromDate = "1.5.19", categoryName = "Offerte", monthlyBudget = 50, userName = "Max Meier"]
            ,[fromDate = "1.5.18", categoryName = "Vertrag", monthlyBudget = 20, userName = "Fritz Peter"]
            ,[fromDate = "4.9.17", categoryName = "Offerte", monthlyBudget = 10, userName = "Fritz Peter"]
            ,[fromDate = "1.1.2000", categoryName = "Demo-Account", monthlyBudget = 100, userName = "Paul Turbo"]
            ,[fromDate = "19.1.2022", categoryName = "Offerte", monthlyBudget = 20, userName = "Paul Turbo"]
            ,[fromDate = "12.8.2020", categoryName = "Offerte", monthlyBudget = 50, userName = "Paul Turbo"]
            ,[fromDate = "2.3.17", categoryName = "Calls", monthlyBudget = 1000, userName = "Paul Turbo"]
            ,[fromDate = "2.5.17", categoryName = "Calls", monthlyBudget = 1100, userName = "Paul Turbo"]
            ,[fromDate = "2.6.17", categoryName = "Calls", monthlyBudget = 200, userName = "Paul Turbo"]
            ,[fromDate = "3.7.17", categoryName = "Calls", monthlyBudget = 50, userName = "Paul Turbo"]
            ,[fromDate = "1.1.2001", categoryName = "Demo-Account", monthlyBudget = 50, userName = "Paul Turbo"]
            ,[fromDate = "1.2.02", categoryName = "Demo-Account", monthlyBudget = 70, userName = "Paul Turbo"]
        }
    ),
    chgTypes = Table.TransformColumnTypes(Source,{{"fromDate", type date}, {"categoryName", type text}, {"userName", type text}, {"monthlyBudget", type number}}),
    groupRows = Table.Group(chgTypes, {"userName", "categoryName"}, {{"data", each _, type table [userName=nullable text, categoryName=nullable text, fromDate=nullable date, monthlyBudget=nullable number]}}),
    sortNestedRows = Table.TransformColumns(groupRows, {"data", each Table.Sort(_ ,{{"userName", Order.Ascending}, {"categoryName", Order.Ascending}, {"fromDate", Order.Ascending}})}),
    addFirstIndex = Table.TransformColumns(sortNestedRows, {"data", each Table.AddIndexColumn(_, "Index1", 1, 1)}),
    addSecondIndex = Table.TransformColumns(addFirstIndex, {"data", each Table.AddIndexColumn(_, "Index0", 0, 1)}),
    expandNestedDataCols = Table.ExpandTableColumn(addSecondIndex, "data", {"fromDate", "monthlyBudget", "Index1", "Index0"}, {"fromDate", "monthlyBudget", "Index1", "Index0"}),
    mergeOnSelf = Table.NestedJoin(expandNestedDataCols, {"userName", "categoryName", "Index1"}, expandNestedDataCols, {"userName", "categoryName", "Index0"}, "Expanded data", JoinKind.LeftOuter),
    expandFromDateRename = Table.ExpandTableColumn(mergeOnSelf, "Expanded data", {"fromDate"}, {"toDateRaw"}),
    addToDate = Table.AddColumn(expandFromDateRename, "toDate", each
        let Date.Today = Date.From(DateTime.LocalNow()) in
        if [fromDate] > Date.Today and [toDateRaw] = null then Date.AddMonths([fromDate], 1)
        else if [toDateRaw] = null then Date.Today
        else Date.AddDays([toDateRaw], -1)
    ),
    addMonthsBetween = Table.AddColumn(addToDate, "monthsBetween", each
        Number.Round(
            Number.From(([toDate] - [fromDate]) / ( 365.25 / 12 ))
            ,0 
        )
    ),
    addDateList = Table.AddColumn(addMonthsBetween, "dateList", each
        List.Transform(
            {
                 Number.From([fromDate])..
                 if [toDate] = null then Number.From(Date.AddMonths([fromDate], 1))
                 else Number.From([toDate])-1
            }, each Date.From(_)
        )
    ),
    remUnusedCols = Table.RemoveColumns(addDateList,{"Index1", "Index0", "toDateRaw"})
in
    remUnusedCols