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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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,128
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
122
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,114,607
Messages
5,548,958
Members
410,885
Latest member
I Hate Computers
Top