Custom query to update the table

[henryvu93]

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

[v-caliao-msft]

@henryvu93,

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

[henryvu93]

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

[MarcelBeug · Accepted solution]

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.

[henryvu93]

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

[MarcelBeug]

Please note that queries in Power Query never actually update anything: they only create new values.

So it is not the case that your source data is actually changed, but the query applies the corrections from the Excel table, which will be done again at each refresh of the data.

So the result of the query is table, compiled from your source data with corrections applied,

It is not an adjusted source and there is no update query you might throw away after it has run.

So you need to keep the original data andthe corrections.

[henryvu93]

Hi Marcel,

Thanks for your reply! It's crystal clear now 

Regards,

Henry