Forum Discussion
Custom query to update the table
Hi guys,
I have a source table which was loaded from the database. In this table, I have some incorrect values I need to update. I also loaded an Excel table, which has the correct values, into PowerBI . Just wonder if it is possible to write a custom query to find the correct values (matching date, id & category) to update the source table? Thank you very much!
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.
6 Replies
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.
