Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Expression.Error: Access field by index in table

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

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

     

     

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      BA_Pete
      Super User
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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