Forum Discussion

pranay_singh's avatar
pranay_singh
Frequent Visitor
2 years ago
Solved

Custom Column to have a record of calculation from multiple columns

Hello,

 

I have a question and looking for a solution. I am not sure even if its possible.
I have a function which I use to calculate NetworkDays = (StartDate as datetime, EndDate as datetime, HolidayList as list) as number

This already works. If I use add column with invoking a custom function and selecting the column with the dates.

What I need:

I have a table with more than 50 columns and need to calculate network days for most of them. I was hoping if I can do it in one go and save in a record in a new column which I can split later if needed.

Sample Date:

So my Start date column name to be used in the Network days function always starts with '_Planning__FIRST_' and ends with 'Real start date' and my End date column name always starts with '_Planning__LAST_' and ends with 'Real end date'. and my holidays list is always '#"US Holidays","US Holidays"'.

I want something like a loop which compares the middle value like '_Planning__FIRST_Phase 1 Real start date' = '_Planning__LAST_Phase 1 Real end date' as the middle Phase names are always same. Then I perform the Neworkdays function and store it in the record with the Field Name in the record being the Phase name

Something Like this: but in a loop so that I do not need to do for every single column calculation

[
Phase 1 = NetworkDays ([#"_Planning__FIRST_Phase 1 Real start date]" as datetime,[ #"_Planning__LAST_Phase 1 Real end date"] as datetime,Table.Column(#"US Holidays","US Holidays") as list)
Phase 2 =  NetworkDays ([#"_Planning__FIRST_Phase 2 Real start date]" as datetime,[ #"_Planning__LAST_Phase 2 Real end date"] as datetime,Table.Column(#"US Holidays","US Holidays") as list)
.
.
.
]

 

 

Number NameReferenceStatus (Request)Creation Date (Project)Creation Date (Request)_Planning__FIRST_Phase 1 Real start date_Planning__LAST_Phase 1 Real end date_Planning__FIRST_Phase 2 Real start date_Planning__LAST_Phase 2 Real end date_Planning__FIRST_Phase 3 Real start date_Planning__LAST_Phase 3 Real end dateDesised Column of Record
132BucketFinancials: Option 1Complete11/05/202111/05/202111/05/202111/08/2021nullnullnullnull[ Phase 1 = 3, Phase 2 = 4, ...]
132BucketFinancials: Option 1Deleted11/05/202111/05/202111/05/202211/08/202211/05/2021nullnullnull 
133BucketFinancials: Option 1Complete11/05/202111/05/202111/05/202311/08/202311/05/202111/08/2023nullnull 
134ModFinancials: Option 1Complete11/05/202111/08/202111/05/202411/08/202411/05/202111/08/2024nullnull 
135ModFinancials: Option 1Complete11/08/202111/08/202111/05/2025null11/05/2021nullnullnull 
36ScentsFinancials: Option 1Complete11/08/202111/08/2021nullnullnullnullnullnull 
37StoresFinancials: Option 1Complete11/08/202111/08/2021nullnullnullnullnullnull 
Need Help
Tips and Tricks
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi pranay_singh,

     

    You can invoke this custom function on your table to add the Desired Column to your table.

    Before invoking it, replace NetworkDays with the actual name of your function query to calculate networkdays. Furthermore, make sure your input parameter types are nullable - if that is not already the case. 

     

    I would also suggest to load the Holidays list into memory and refer to that instead of calling Table.Column for each row within this function. Simply, create a new query called; HolidayList and assign that this value: List.Buffer(Table.Column(#"US Holidays","US Holidays")) then update the function logic below.

     

    (t as table ) as table =>
let
    First = List.Select( Table.ColumnNames( t ), each Text.Contains(_, "__FIRST" )),
    Transform = List.Transform( First, each 
        let s = Text.Trim( Text.BetweenDelimiters(_, "_Planning__", " Real")) in 
        {_, Replacer.ReplaceText(Replacer.ReplaceText(_, "FIRST", "LAST"), " start ", " end "), Text.AfterDelimiter(s, "_")} 
    ),
    AddCol = Table.FromRecords( List.Transform( Table.ToRecords( t ), each _ & [Desised Column =
            List.Accumulate(
                Transform,
                [],
                (s, a)=> Record.AddField(s, 
                    a{2}, 
                    /* replace NetworkDays with the actual name of your function query! */
                    /* make sure your parameters are of a nullable type*/
                    NetworkDays( Record.Field(_,a{0}), Record.Field(_,a{1}), 
                        Table.Column(#"US Holidays","US Holidays") /* HolidayList */
                    )
                )
        )]
    ))
in
    AddCol

     

    I hope this is helpful

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi pranay_singh,

     

    You can invoke this custom function on your table to add the Desired Column to your table.

    Before invoking it, replace NetworkDays with the actual name of your function query to calculate networkdays. Furthermore, make sure your input parameter types are nullable - if that is not already the case. 

     

    I would also suggest to load the Holidays list into memory and refer to that instead of calling Table.Column for each row within this function. Simply, create a new query called; HolidayList and assign that this value: List.Buffer(Table.Column(#"US Holidays","US Holidays")) then update the function logic below.

     

    (t as table ) as table =>
let
    First = List.Select( Table.ColumnNames( t ), each Text.Contains(_, "__FIRST" )),
    Transform = List.Transform( First, each 
        let s = Text.Trim( Text.BetweenDelimiters(_, "_Planning__", " Real")) in 
        {_, Replacer.ReplaceText(Replacer.ReplaceText(_, "FIRST", "LAST"), " start ", " end "), Text.AfterDelimiter(s, "_")} 
    ),
    AddCol = Table.FromRecords( List.Transform( Table.ToRecords( t ), each _ & [Desised Column =
            List.Accumulate(
                Transform,
                [],
                (s, a)=> Record.AddField(s, 
                    a{2}, 
                    /* replace NetworkDays with the actual name of your function query! */
                    /* make sure your parameters are of a nullable type*/
                    NetworkDays( Record.Field(_,a{0}), Record.Field(_,a{1}), 
                        Table.Column(#"US Holidays","US Holidays") /* HolidayList */
                    )
                )
        )]
    ))
in
    AddCol

     

    I hope this is helpful

    • pranay_singh's avatar
      pranay_singh
      Frequent Visitor
      2 years ago

      Hi, I have my values a nullable

      But while running the above query I am getting this error:

       

      • m_dekorte's avatar
        m_dekorte
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        Hi pranay_singh 

         

        OPTION 1: deal with nulls, prevent errors

        "Replace NetworkDays with the actual name of your function query to calculate networkdays. Furthermore, make sure your input parameter types are nullable - if that is not already the case."

         

        NetworkDays = (StartDate as nullable datetime, EndDate as nullable datetime, optional HolidayList as list) as number =>

         

        Within the function body you can determine how to deal with nulls, for example wrapping a conditional statement :

        NetworkDays = (StartDate as nullable datetime, EndDate as nullable datetime, optional HolidayList as list) as number =>

        if StartDate = null then null /* just for illustration */

        else if EndDate = null then 0 /* just for illustration */

        else let 

        /* your function body goes here*/

        in

         

        OPTION 2: handle errors

        An alternative is to leave everything as is and incorporate error handling by wrapping try-otherwise around the function invocation, for example. This allows you to specify a default value in case an error is encountered, to illustrate: 

        try NetworkDays([Start], [End], Holidays) otherwise null

         

        I hope this is helpful

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi pranay_singh, check this:

     

    Add your holidays at Holidays step (or refer you your query with holidays, but keep in mind that your HolidaysTable must contains [Date] column)

     

     

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vdJbC4IwFAfwr3LY81B3sSTopaK3KOhRfBAdJK5Ncn7/JhhJObMLPe2cXfj/2BbHiDCKMFo1WSmMLbaFSlVWpLJewL4yhVZA7PRanyspjLAlIX4Q+jSg5GUT3RrVSOkaYjic0loAgSUw3DXUNhyD53kJSvBU5Ua0xnwakvaR9HHbIBU6C/v5jbE+hjnvkjlJ3NY7nX/miZ48vL/CnWe40xO+6YnGPeE9YeozsZktj5lQpv5SMfZ7B5PnbbLRF/HX5OQK", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Number " = _t, Name = _t, Reference = _t, #"Status (Request)" = _t, #"Creation Date (Project)" = _t, #"Creation Date (Request)" = _t, #"_Planning__FIRST_Phase 1 Real start date" = _t, #"_Planning__LAST_Phase 1 Real end date" = _t, #"_Planning__FIRST_Phase 2 Real start date" = _t, #"_Planning__LAST_Phase 2 Real end date" = _t, #"_Planning__FIRST_Phase 3 Real start date" = _t, #"_Planning__LAST_Phase 3 Real end date" = _t, #"Desised Column of Record" = _t]),
    Holidays = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzDTNzIwMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    // Remove locale if necessary
    ChangedTypeHolidays = Table.TransformColumnTypes(Holidays,{{"Date", type date}}, "en-US"),
    StepBack = Source,
    PlanningDateColumns = List.Select(Table.ColumnNames(StepBack), (x)=> List.Contains({"Real start date", "Real end date"}, x, (y,z)=> Text.EndsWith(z, y))),
    ToTable = Table.FromList(PlanningDateColumns, (x)=> {x}),
    Ad_Phase = Table.AddColumn(ToTable, "Phase", each Number.From(Text.BetweenDelimiters([Column1], "Phase", "Real")), Int64.Type),
    GroupedRows = Table.Group(Ad_Phase, {"Phase"}, {{"All", each Table.FromList({[Column1]}, (x)=> x, {"Start", "End"}), type table}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Start", "End"}, {"Start", "End"}),
    ToRowsBuffered = List.Buffer(Table.ToRows(ExpandedAll)),
    StepBack2 = StepBack,
    // Remove locale if necessary
    ChangedTypePlanningCols = Table.TransformColumnTypes(StepBack2, List.Transform(PlanningDateColumns, (x)=> {x, type date}), "en-US"),
    Ad_NetworkDays = Table.AddColumn(ChangedTypePlanningCols, "Phase Network Days", each 
        List.Accumulate(
            ToRowsBuffered,
            [],
            (s,c)=> s & Record.AddField(s, "Phase " & Text.From(c{0}), 
                                        [ start = Record.Field(_, c{1}),
                                          end = Record.Field(_, c{2}),
                                          lstDates = if List.Contains({ start, end }, null) then {null} else List.Dates(start, Duration.TotalDays(end - start)+1, #duration(1,0,0,0)),
                                          removeHolidays = List.RemoveMatchingItems(lstDates, ChangedTypeHolidays[Date]),
                                          result = if List.NonNullCount(removeHolidays) = 0 then null else List.NonNullCount(removeHolidays)
                                        ][result] ) ), type record )
in
    Ad_NetworkDays

     

    • pranay_singh's avatar
      pranay_singh
      Frequent Visitor
      2 years ago

      Hi,

      Is it possible to use my own NetworkDays function and call it inside this columns?
      NetworkDays ([#"_Planning__FIRST_Phase 1 Real start date]" as datetime,[ #"_Planning__LAST_Phase 1 Real end date"] as datetime,Table.Column(#"US Holidays","US Holidays") as list)