Forum Discussion
Overwrite columns
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"
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 )
:(
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"
Thank You!
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?
