Forum Discussion
Custom query to update the table
Steps to take:
Merge your source table with the Excel table.
Expand the nested Excel tables.
Add a custom column that states:
if Excel-unit-cost <> null then Excel-unit-cost else Source-unit-cost.
Remove original source-unit-cost and rename the new column to unit cost.
You may want to reorder your columns and remove the columns you no longer need.
You can create a calculate column to get the correct value from another base on your condition. Could you please share us some sample data and elaborate the logic to replace correct value?
Regards,
Charlie Liao
Hi Charlie,
Thanks for your reply! Basically I have a source table where I have sales data based on Year & Month. Additionally, I will have columns "CategoryID, SubcategoryID, Quantity, UnitCost, UnitPrice, Total Cost, Total Price, Margin, etc.). However, the unitcost in some columns is wrong. I have another table which is an Excel file with the correct unitcost (screenshot below)
What I want to do is to write an "update" query to update correct unitcosts in the Excel file into the source table (with the corresponding Year, Month, Category & Subcategory). The source table will have a lot more categories & subcategories and 1 subcategory can be bought multiple times in the month.Regards,
Henry
Steps to take:
Merge your source table with the Excel table.
Expand the nested Excel tables.
Add a custom column that states:
if Excel-unit-cost <> null then Excel-unit-cost else Source-unit-cost.
Remove original source-unit-cost and rename the new column to unit cost.
You may want to reorder your columns and remove the columns you no longer need.
Hi Marcel,
Thanks for the detailed reply! I would assume in this case the best way is to do it step-by-step like you mentioned? Is it possible to actually write an SQL query at any stage to update the table? Or we are actually following these steps and have the "M" script for future use?
Regards,
Henry
