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

[kpope713]

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.

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"

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

 

[C Moore]

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]

It looks much much better if you'll use code tags [CODE=pq]your code here[/CODE]

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]

Could I suggest you search for self referencing queries. It might be the solution you are looking for.

 

[kpope713]

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.