none
Custom Column to modify an existing Date Column RRS feed

  • Question

  • I am doing a power query where I need to create a custom querythat creates an ActiveCycle column from a Active date column as follows:

    • if the day of the Active date is 1-10, the cycle is 10
    • if the day of the Active date is 11-20, the cycle is 20
    • if the day of the Active date is >21, the cycle is 30,

    so if a  Active date is 10/15/2016 the ActiveCycle would be 10/20/2016, 10/25/2016 would be 10/30/2016. 

    Here is my custom column code:

    = Table.AddColumn(#"Filtered Rows", "Custom", each if Date.Day[Active] > 20 then [ActiveCycle] = Date.Year[Active] & Date.Month[Active] & 30 else
    if Date.Day[Active] > 10 then [ActiveCycle] = Date.Year[Active] & Date.Month[Active] & 20 else
    [ActiveCycle] = Date.Year[Active] & Date.Month[Active] & 10)

    When it runs, I get this error:

    Expression.Error: We cannot apply field access to the type Function.
    Details:
        Value=Function
        Key=Active

    I'm not sure what this is complaining about.   Any suggestions?

    Tuesday, December 6, 2016 7:34 AM

Answers

  • I doubt this is the best way to do what you are doing. You should consider creating a calendar table I stead of adding to your data table. That said, you can't concatenate numbers. You could either convert the numbers to text first, then concatenate or you can multiply the numbers by base 10 numbers to shift the digit register

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Tuesday, December 6, 2016 8:39 AM
  • You can also use a formula like in the code below, which also takes into account that there is no such thing as February 30.

    let
        Source = Excel.CurrentWorkbook(){[Name="DatesTable"]}[Content],
        Typed = Table.TransformColumnTypes(Source,{{"SomeDate", type date}}),
        Cycled = Table.AddColumn(Typed, "ActiveCycle", each #date(Date.Year([SomeDate]),Date.Month([SomeDate]),
                     List.Min({Date.DaysInMonth([SomeDate]),10*List.Min({3,Number.RoundDown((Date.Day([SomeDate])+9)/10)})})), type date)
    in
        Cycled


    Tuesday, December 6, 2016 10:38 AM

All replies

  • I doubt this is the best way to do what you are doing. You should consider creating a calendar table I stead of adding to your data table. That said, you can't concatenate numbers. You could either convert the numbers to text first, then concatenate or you can multiply the numbers by base 10 numbers to shift the digit register

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Tuesday, December 6, 2016 8:39 AM
  • You can also use a formula like in the code below, which also takes into account that there is no such thing as February 30.

    let
        Source = Excel.CurrentWorkbook(){[Name="DatesTable"]}[Content],
        Typed = Table.TransformColumnTypes(Source,{{"SomeDate", type date}}),
        Cycled = Table.AddColumn(Typed, "ActiveCycle", each #date(Date.Year([SomeDate]),Date.Month([SomeDate]),
                     List.Min({Date.DaysInMonth([SomeDate]),10*List.Min({3,Number.RoundDown((Date.Day([SomeDate])+9)/10)})})), type date)
    in
        Cycled


    Tuesday, December 6, 2016 10:38 AM