Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Keep added columns in the table loaded from a query updated weekly

I am using power query to transform the data from a shipment report for reporting purpose. I created a query from the report and loaded the query to a table in the worksheet. I then added two columns to the table for me to take notes for tacking purpose. The shipment report is updated weekly, so I just change the data source of the query every week to automate the data transformation process. However, the notes I took disappeared every time after I updated the query since the two columns I added are not included in the report and could not be added to the report. Is there a way to have the two columns and the notes I take remain in my table that is connected to the query even if I update my data source? 

Need Help
Tips and Tricks

3 Replies

  • Nolock's avatar
    Nolock
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous,

    am I right that you are in Excel?

    Excel doesn't allow to create a table by hand like PowerBI via Enter data. But there is a workaround. Create a new table in PowerQuery https://docs.microsoft.com/en-us/powerquery-m/sharptable with your notes and join this table with the query which gets the data from an external source. Then your notes will be a part of your final table even after a data refresh.

    A table with 2 columns ID and Note and 2 rows.

    let
    Source = #table(
        {"id", "note"}, 
        {
            {1, "abc"},
            {1, "xyz"}
        }
    )
in
    Source

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Nolock,

       

      Thanks for your suggestion. But I might still have an issue. That is, my notes need to be connected to cetain row in my dataset. Since new rows are appended to the data source every week which I don't have access to edit, my notes column can be carried over to the updated table. 

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Do you have data in excel, then you add two columns in excel sheet table for notes?

    Everyweek, new data is added row by row in the original excel sheets, you click on "refresh" button from Power BI Desktop to get new data into Power BI, right?

     

    Your issue is : after updates, the two added columns for notes is missing in Power BI?

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.