Custom Column to have a record of calculation from multiple columns

[pranay_singh]

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

Name

Reference

Status (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 date

Desised Column of Record

132

Bucket

Financials: Option 1

Complete

11/05/2021

11/05/2021

11/05/2021

11/08/2021

null

null

null

null

[ Phase 1 = 3, Phase 2 = 4, ...]

132

Bucket

Financials: Option 1

Deleted

11/05/2021

11/05/2021

11/05/2022

11/08/2022

11/05/2021

null

null

null

133

Bucket

Financials: Option 1

Complete

11/05/2021

11/05/2021

11/05/2023

11/08/2023

11/05/2021

11/08/2023

null

null

134

Mod

Financials: Option 1

Complete

11/05/2021

11/08/2021

11/05/2024

11/08/2024

11/05/2021

11/08/2024

null

null

135

Mod

Financials: Option 1

Complete

11/08/2021

11/08/2021

11/05/2025

null

11/05/2021

null

null

null

36

Scents

Financials: Option 1

Complete

11/08/2021

11/08/2021

null

null

null

null

null

null

37

Stores

Financials: Option 1

Complete

11/08/2021

11/08/2021

null

null

null

null

null

null

[dufoq3]

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]

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)

[m_dekorte · Accepted solution]

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]

Hi, I have my values a nullable

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

[m_dekorte]

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