Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pranay_singh
Frequent Visitor

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 
1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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

View solution in original post

5 REPLIES 5
dufoq3
Super User
Super User

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)

 

dufoq3_1-1718815194446.png

 

Result:

dufoq3_0-1718815073953.png

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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
Super User
Super User

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

Hi, I have my values a nullable

pranay_singh_0-1718815918503.png

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

pranay_singh_1-1718815939710.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors