Forum Discussion
Custom Column to have a record of calculation from multiple columns
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 AddColI hope this is helpful
Community ChampionHi 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
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)