Search code examples
typespowerquerynested-lists

Power Query: Change the datatype of elements in a nested list?

I'm trying to do a interpolation for which I found a code example here https://stackoverflow.com/a/69959544/19328275. It is written for integers, but I need decimals. I was able to change the code until the creation of the column "dayList", which contains lists. If I write #"Added Custom1" = Table.AddColumn(#"Added Custom", "dayList", each {[BusinessDays]..[shifted BusDays]-0.1}) instead of #"Added Custom1" = Table.AddColumn(#"Added Custom", "dayList", each {[BusinessDays]..[shifted BusDays]-1}) (subtracting 0.1 instead of 1), I get an Expression.Error: Number is out of range of a 32 bit integer value. How can I change the datatype of the list elements within this step?

Solution

  • The specs for using expression..expression as a list-generator specifically mention whole numbers. See https://learn.microsoft.com/en-us/powerquery-m/m-spec-values#list:

    To include a sequence of whole number in a list, the a..b form can be used
    { 1, 5..9, 11 } // { 1, 5, 6, 7, 8, 9, 11 }

    However, you can use [List.Numbers] instead.