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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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"
 
Upvote 0
Could I suggest you search for self referencing queries. It might be the solution you are looking for.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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