Expression.Error: Access field by index in table

[Anonymous]

Hi Power BI community

My goal:

• data input: Excel table with monthly budget per employee from a specific date. New budget can be added on a random following date.
• Power Query transforms the input Excel budget table to budget per employee calculated per day.

How I try to do this:

1. group by employee
2. Add index to grouped employee tables
3. add a "toDate" field to each row in tables -> here I fail
4. create rows between fromDate and toDate for each day with calculated budget per day
5. expand tables and clean up

 let
    Source = Excel.Workbook(Web.Contents("https://Budget_Excel.xlsx"), null, true),
    userBudget_Sheet = Source{[Item="userBudget",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(userBudget_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"fromDate", type date}, {"username", type text}, {"categoryName", type text}, {"monthlyBudget", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"fromDate", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"username"}, {{"BudgetGroupByUser", each _, type table [fromDate=nullable date, username=nullable text, categoryName=nullable text, budget=nullable number]}}),
    #"Added Index1" = Table.AddColumn(#"Grouped Rows", "BudgetIndex", each Table.AddIndexColumn([BudgetGroupByUser], "Index1", 1, 1, Int64.Type)),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "foo", each Table.AddColumn([BudgetIndex], "Custom", each [BudgetIndex]{[Index1]}[fromDate]))
in
    #"Added Custom"

Thank you so much for your support.

Best regards

olk

[Anonymous]

Hi @BA_Pete 

thank you very much for your help.

Please check out the sample data as JSON below.

My thought were:

1. sort table by fromDate
2. group by "username"
3. group "groupByUsername" table again by "categoryName"
4. add Index1 on "groupByUsernameAndcategoryName"
5. add column "toDate" on groupByUsernameAndcategoryName {Index1][fromDate]

[
    {"fromDate": "fromDate", "categoryName": "categoryName", "monthlyBudget": "monthlyBudget", "username": "username"}
    ,{"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"}
]

 Best regards

Olk

[BA_Pete · Accepted solution]

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

[Anonymous]

Helllo @BA_Pete 

that is a great example. Exactly what I was looking for.

Thank you very much and have a great day.

Best regards

olk

[BA_Pete]

Hi @Anonymous ,

I think there's a whole lot going on here. My immediate guess is that it rests with the double-use of Table.AddColumn, rather than using Table.TransformColumns, but can't be certain without testing.

Can you provide an example of this (your source table) either as a copyable table, or (preferably) as a JSON source (use Enter Data in Power Query) please?:

Then try and provide a basic example of what your expected end-table looks like.

Thanks,

Pete