i have a query that i have been using that transforms some project data. i wanted to add an extra column to identify which projects were active. i copied part of the existing query, which added a column earlier in the process, changing it to fit the new details i needed. While it ran, and gives me the answers i need, it completely removed all the previous steps in the query. Is anyone able to advise why and how to better achieve the last step (identification of the active projects ).
so that works well. however, if i add in the following #"Expanded Table1", all the steps created above, disappear.
why does this happen?
Code:
let
Source = Table.Combine({ExtXLFileT1Contracts, #"RefTblCalculation PPR", #"P6DataNew V2", ExtXLFileOtherActivities}),
#"Removed Columns" = Table.RemoveColumns(Source,{"Tender Out Date for Design Activities"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Contract Owner", "Contract", "Reference", "Bundle", "Description", "Parties", "End Date", "Business Need", "Contract Status", "Phase", "Contract Type", "Contract Type Summary", "Contract Value Risk Matrix - Code", "Contract Terms and Conditions - Code", "Contract Manager", "Contract Sponsor - Code", "Contract Sponsor", "Start Date", "Section", "Category", "Category Description", "Forward Plan - Code", "Forward Plan", "Contract Manager - Code", "Total Length Days", "Business Unit", "Branch", "# Days for Next Activity", "Start Date for Next Activity", "End Date for Next Activity", "Procurement officer allocation%", "Progress Comment", "% Complete"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Reordered Columns",{"Contract Owner", "Contract", "Reference", "Bundle", "Description", "Parties", "End Date", "Business Need", "Contract Status", "Phase", "Contract Type", "Contract Type Summary", "Contract Value Risk Matrix - Code", "Contract Terms and Conditions - Code", "Contract Manager", "Contract Sponsor - Code", "Contract Sponsor", "Start Date", "Section", "Category", "Category Description", "Forward Plan - Code", "Forward Plan", "Contract Manager - Code", "Total Length Days", "Business Unit", "Branch", "Next Activity", "# Days for Next Activity", "Start Date for Next Activity", "End Date for Next Activity", "Procurement officer allocation%", "Progress Comment", "% Complete"}),
'///// above section appends four tables and sorts columns into required order
#"Added Custom1" = Table.AddColumn(#"Reordered Columns1", "End of Week of Start Date", each Date.EndOfWeek([Start Date for Next Activity],0)),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"End of Week of Start Date", "Next Activity Start Date (EOW)"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Next Activity End Date (EOW)", each Date.EndOfWeek([End Date for Next Activity],0)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Next Activity Start Date (EOW)", type date}, {"Next Activity End Date (EOW)", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Start Date for Next Activity", "Activity Start"}, {"End Date for Next Activity", "Activity End"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Procurement officer allocation%", Percentage.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"Unknown",Replacer.ReplaceValue,{"Contract Manager - Code"}),
#"Uppercased Text" = Table.TransformColumns(#"Replaced Value",{{"Contract Manager - Code", Text.Upper, type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Uppercased Text",null,0,Replacer.ReplaceValue,{"% Complete"}),
'//// above section renames columns, changes type, and replaces values. basically formatting
today = Date.From(DateTime.LocalNow()),
Result = Table.AddColumn(#"Replaced Value1", "Indicator", each let
startdate = [Activity Start],
enddate = [Activity End],
progress = if [#"% Complete"] = null then 0 else [#"% Complete"]/100,
plannedduration = enddate - startdate,
targetdate = Date.AddDays(startdate, Number.Round(Duration.Days(plannedduration)*progress)),
daysfromtarget = Duration.Days(targetdate - today)
in
if progress = 1 then 1 else
if today > enddate then 3 else
if Number.Abs(daysfromtarget) <= 14 then 2 else
if daysfromtarget > 14 then 1 else 3),
'//// with all projects, assign 1, 2, 3 or 10 to identify ontime, risk of slipping, out of time, and projects with no dates assigned (below)
#"Replaced Errors" = Table.ReplaceErrorValues(Result, {{"Indicator", 10}}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Errors", {"Indicator"}, #"TblTrafficLights (2)", {"Indicator"}, "Table", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"URL"}, {"Table.URL"})
'//// merge to incorporate URLs for traffic lights
in
#"Expanded Table1"so that works well. however, if i add in the following #"Expanded Table1", all the steps created above, disappear.
Code:
Result2 = Table.AddColumn(#"Expanded Table1", "ActiveProject", each let
activeperiodstartdate = Date.AddDays(today, - 183),
activeperiodenddate = Date.AddDays(today, 730)
in
if ([Activity Start] < activeperiodstartdate) and ([Activity End] > activeperiodenddate)
or ([Activity Start] > activeperiodstartdate) or ([Activity End] < activeperiodenddate) then "Y" else "N"),
#"Replaced Errors2" = Table.ReplaceErrorValues(Result2, {{"ActiveProject", "N"}})
in
Result2why does this happen?
