Overwrite columns

[Recoba88]

Hi,

When I add column only in excel (the data loaded from power query) and after it I add another column in power query. The column from power query overwrites the column I added in excel.

How can I change it?

Thanks!

[MarcelBeug]

Your question isn't very clear, but maybe you want something similar as illustrated in this video.

In that case, data was imported from SQL and comments were added in Excel.

In order to keep the comments in Excel aligned with the data from SQL, the query is adjusted so the input for the query is the same table as its output. This is merged with the data from SQL.

[Recoba88]

Thanks, seems to me very complicated,

Is there another way?

Thanks

Igor

[MarcelBeug]

Instead of clarifying your question, you can of course also post the same unclear question on another forum....

[Recoba88]

For Example:

Power Query Data

Data from workbook after I added custom column in excel

Data in workbook after I added anothe column in power query and  refreshed the workbook 

 Any Idea?

[Eric_Zhang]

---

@Recoba88 wrote:

For Example:

Power Query Data

Data from workbook after I added custom column in excel

Data in workbook after I added anothe column in power query and  refreshed the workbook 

 Any Idea?

---

@Recoba88

I don't see the "overwrite" in Power BI Desktop, do you use the latest version?

Click refresh

[Recoba88]

Hi Eric,

I tried again and still it didn't work.

I use Powerquery from excel not from BI Desktop .

My Excel version is : 16.0.4266.1001

[Recoba88]

You can see the column "custom2" overwrites column "custom"

[MarcelBeug]

That's quite logical, as the table is output from Power Query, so if you adjust the table, than all your manual modifications will be lost once the query is refreshed.

You can prevent that to adjust the Source of your query to be that same table (instead of the original source), meaning that the input for the query will be the same table as the output.

However, you can't add further columns to the table, as these will be added each time the query is refreshed, resulting in an error as the column already exist.

Maybe you can explain further what you actually want to achieve, what is your scenario? It is already strange that your original table originates from Power Query. Typically you would have an Excel table (or an external source) as input for Power Query.

I can understand if you have an external data source with updates you want to add to your table with manual added comments, but that would be additional rows (as illustrated in the video I linked previously) and not additional columns.

[Recoba88]

The data I load to PowerQuery comes from Sharepoint table. 

I load it to excel worksheet and want to add a custom column with some function (It is to complicated to do it in power query editor and I prefer to do it in excell).

I want that my custom column will not be deleted when I press refresh power query (even though people my add more columns in Sharepoint that will reflects in Powerquery )

😞

[MarcelBeug · Accepted solution]

In that case you need to merge your table (with comments) and the information from Sharepoint in a query which sends it output back to your table (which serves both as input and as output).

In order to do that, you need to have a key column (or a set of key columns) you can use to merge both tables.

So your query structure is like:

get table from Sharepoint

merge with Excel table and expand (only your comment column so the comment will be added to the table from Sharepoint)

And the output of this query is written to the Excel table.

Edit: example of a working query how it looks like in the end.

It requires some steps to set it up, as illustrated in the video that I linked in a previous post.
The name of the query is ExcelTable, so the output is used as input with the next refresh.

let
    Source = SharepointTable,
    TableWithComments = Excel.CurrentWorkbook(){[Name="ExcelTable"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"Header 1"},TableWithComments,{"Header 1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Comments"}, {"Comments"})
in
    #"Expanded NewColumn"

[freelensia]

Hi @MarcelBeug,

Thank you for the elegant solution. I have been using it for some time now.

However, there is something that has always troubled me. After I implement your solution for the first time, Excel always creates another set of the "comments" columns. I have to manually delete those columns in the resulting table in Excel.

Coming to think about it, PQ is doing the right thing in terms of code. When you ask it to Expand Columns after the merge, it does exactly that and creates the extra set of columns!

How do we prevent that?

[Recoba88]

Thank You!