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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
539
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
6,796
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
115
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,109,406
Messages
5,528,591
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top