Forum Discussion

henryvu93's avatar
henryvu93
Helper I
8 years ago
Solved

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 P...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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's avatar
henryvu93
Helper I
8 years ago

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's avatar
MarcelBeug
Community Champion
8 years ago

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's avatar
    henryvu93
    Helper I
    8 years ago

    Hi Marcel,

     

    Thanks for your reply! It's crystal clear now :smileyhappy:

     

    Regards,

    Henry