Add custom column to source table instead of load to new table

[abdulbasitb]

Say I have a table of 4 columns and I use power query to add a 5th custom column.
Instead of loading the query to a fresh new excel table of 5 columns, can I instead, only load the 5th column to the source table in the worksheet?

One reason could be to allow editing source data and also see the custom column in the same place.

 

[sandy666]

so add 5th column in PQ
add Index
select 5th column
remove other columns
load this single column to the sheet as 5th column of your source data

 

[abdulbasitb]

so add 5th column in PQ
add Index
select 5th column
remove other columns
load this single column to the sheet as 5th column of your source data

When I try that it creates another table next to the existing table instead of adding a column to the existing.

 

[sandy666]

post a link to the shared excel file with your example

 

[abdulbasitb]

Below is the link to example file (cannot post attachments)
mediafire.com/file/mfsuam9na6tt4hm/Add_custom_column.xlsx/file

 

[sandy666]

what is wrong with your result? it seems ok

 

[abdulbasitb]

The green column is actually become a new independent table that would become difficult to manage.
If any work is continued such as addding new columns or rows, the two tables won't always work together. If any data is added on the green side and it's sorted, the blue table won't sort accordingly.

I was wondering if this the best it can be or is there any way to have the resulting table as one, with the custom column?

 

[sandy666]

Green column is a result of your query-table and you can manage in PQ Editor
I strongly suggest to read much more about PowerQuery

If you want manage as you said load the result query-table (not only single column) to the sheet then you can add/remove any column to the source table then refresh and you should see the result.

You cannot mix source table and query-table and manage this as single table because you cannot change any value in query-table , because (again) if you refresh query-table you will get original values.

you've, say three layers:
- source layer
- transform layer (PQ Editor)
- result layer (if you load transform layer to the sheet)

I recommend second line of this post

Have a nice day

 

[abdulbasitb]

That's all I wanted to know. Thanks

 

[sandy666]

You are welcome