adding extra lines to query removed all steps

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,023
Office Version
  1. 365
Platform
  1. Windows
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 ).

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.
PBi.JPG


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

Result2

PBi2.JPG


why does this happen?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I can't see how ForwardPlan can be the result of any of those steps as it isn't even in the code. Can you post the full M-code causing the error in one piece?
 
Upvote 0
hey Rory, here it is.

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"}),

#"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"}),



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),



#"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"}),



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

Result2
 
Upvote 0
I can't see any way that could end up as the result in your picture. Any chance you can put an anonymised file somewhere and post a link here?
 
Upvote 0
bugger. is there a better way to do the second routine? if you were adding in columns one after the other, how would you go about it?
 
Upvote 0
hey rory, i thought i would try to be clever and referenced the Forward Plan table. i duplicated a column to get started and then inserted the result 2 routine. it did the same thing. removed the preceding steps. Doh!
 
Upvote 0
Oh, wait - I just noticed this part at the end:

Power Query:
#"Replaced Errors2" = Table.ReplaceErrorValues(Result2, {{"ActiveProject", "N"}})

in

Result2

That should be:

Power Query:
#"Replaced Errors2" = Table.ReplaceErrorValues(Result2, {{"ActiveProject", "N"}})

in

#"Replaced Errors2"
 
Upvote 0
Solution
oh baby! you da man! i would not have seen that. such a simple mistake too.

Thanks for persevering Rory.

1716558362122.png
 
Upvote 0
It's always the little things! :)

Glad we could help.
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top