How to add open ended column to power query that keeps data after you refresh the data.

kpope713

New Member
Joined
Sep 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have a live data query that we need to be able to put projected additional costs (currently a blank column in the query), and have it keep the inputs after refreshing the data. I need to be able to enter a value into the Total Projected Additions column and have it maintain that value when the sheet is refreshed.

Any ideas? I apologize for the mess of a query below, it's how Power Query wrote it when I did my table changes.


Currently my Odata query looks like this.

Power Query:
let
    Source = OData.Feed("[B]REDACTED[/B]", null, [Implementation="2.0"]),
    PMBudget_table = Source{[Name="PMBudget",Signature="table"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(PMBudget_table,{"OriginalBudgetedQuantity", "RevisedBudgetedQuantity", "UnitRate", "UOM", "AccountGroup", "InventoryID"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Type] = "Expense")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"PotentialRevisedQuantity", "ActualQuantity", "RevisedCommittedQuantity", "CommittedOpenQuantity", "CommittedReceivedQuantity", "CommittedInvoicedQuantity", "CreatedBy", "LastModifiedBy", "Login", "Login_2", "ProjectID_2", "Completed", "BaseType", "ProjectID_3", "ProjectTaskID", "Description_3", "DraftInvoicesAmount", "PendingInvoiceAmount"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"LastModifiedOn", "ProjectID", "TaskID", "CostCode", "Description", "Type", "Description_2", "OriginalBudgetedAmount", "RevisedBudgetedAmount", "RevisedCommittedAmount", "ActualAmount", "CommittedOpenAmount", "CommittedInvoicedAmount", "VarianceAmount"}),
    #"Extracted Date" = Table.TransformColumns(#"Reordered Columns",{{"LastModifiedOn", DateTime.Date, type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Date",{{"Description_2", "Cost Code Description"}, {"LastModifiedOn", "Last Modified"}, {"ProjectID", "Project ID"}, {"TaskID", "Task ID"}, {"CostCode", "Cost Code"}, {"Description", "Project"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Type"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Last Modified", "Project ID", "Project", "Task ID", "Cost Code", "Cost Code Description", "OriginalBudgetedAmount", "RevisedBudgetedAmount", "CommittedInvoicedAmount", "CommittedOpenAmount", "RevisedCommittedAmount", "ActualAmount", "VarianceAmount"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"RevisedCommittedAmount", "Commitments - Revised"}, {"VarianceAmount", "Remaining Balance"}, {"ActualAmount", "Project Costs to Date"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each ([Project] = PROJECT)),
    #"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows1",{"Task ID", "CommittedInvoicedAmount", "CommittedOpenAmount", "Remaining Balance"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"OriginalBudgetedAmount", "Original Estimated Costs"}, {"RevisedBudgetedAmount", "Revised Estimated Costs"}, {"Commitments - Revised", "Revised Commitments"}})
in
    #"Renamed Columns2"

1599572812023.png



I GREATLY appreciate any input any of you gurus may have.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
540
Office Version
  1. 365
Platform
  1. Windows
To enter values, the only way I'm aware of is to have a separate table where you enter data. You could then merge to add the column to your main table.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,074
It looks much much better if you'll use code tags [CODE=pq]your code here[/CODE]
Power Query:
let
    Source = OData.Feed("REDACTED", null, [Implementation="2.0"]),
    PMBudget_table = Source{[Name="PMBudget",Signature="table"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(PMBudget_table,{"OriginalBudgetedQuantity", "RevisedBudgetedQuantity", "UnitRate", "UOM", "AccountGroup", "InventoryID"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Type] = "Expense")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"PotentialRevisedQuantity", "ActualQuantity", "RevisedCommittedQuantity", "CommittedOpenQuantity", "CommittedReceivedQuantity", "CommittedInvoicedQuantity", "CreatedBy", "LastModifiedBy", "Login", "Login_2", "ProjectID_2", "Completed", "BaseType", "ProjectID_3", "ProjectTaskID", "Description_3", "DraftInvoicesAmount", "PendingInvoiceAmount"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"LastModifiedOn", "ProjectID", "TaskID", "CostCode", "Description", "Type", "Description_2", "OriginalBudgetedAmount", "RevisedBudgetedAmount", "RevisedCommittedAmount", "ActualAmount", "CommittedOpenAmount", "CommittedInvoicedAmount", "VarianceAmount"}),
    #"Extracted Date" = Table.TransformColumns(#"Reordered Columns",{{"LastModifiedOn", DateTime.Date, type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Date",{{"Description_2", "Cost Code Description"}, {"LastModifiedOn", "Last Modified"}, {"ProjectID", "Project ID"}, {"TaskID", "Task ID"}, {"CostCode", "Cost Code"}, {"Description", "Project"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Type"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Last Modified", "Project ID", "Project", "Task ID", "Cost Code", "Cost Code Description", "OriginalBudgetedAmount", "RevisedBudgetedAmount", "CommittedInvoicedAmount", "CommittedOpenAmount", "RevisedCommittedAmount", "ActualAmount", "VarianceAmount"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"RevisedCommittedAmount", "Commitments - Revised"}, {"VarianceAmount", "Remaining Balance"}, {"ActualAmount", "Project Costs to Date"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each ([Project] = PROJECT)),
    #"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows1",{"Task ID", "CommittedInvoicedAmount", "CommittedOpenAmount", "Remaining Balance"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"OriginalBudgetedAmount", "Original Estimated Costs"}, {"RevisedBudgetedAmount", "Revised Estimated Costs"}, {"Commitments - Revised", "Revised Commitments"}})
in
    #"Renamed Columns2"
 

peter789

Board Regular
Joined
Nov 20, 2016
Messages
120
Could I suggest you search for self referencing queries. It might be the solution you are looking for.
 

kpope713

New Member
Joined
Sep 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you Sandy! I wasn't aware of the formatting capability.

Peter789, I'm looking into those now and it seems to be what I'm looking for. Thank you for your input.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,869
Messages
5,544,779
Members
410,633
Latest member
ecronic
Top