Forum Discussion
AdministratorAdd Empty Column in Excel power Query
Is there a way to add an empty column within a dataset imported from SharePoint online list for a user input
8 Replies
v-jingzhangCommunity Support
You could add a custom column with null as values. Is this what you want?
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.- Syndicate_Admin
thanks for your reply, but any amount the user keyed in this null column got deleted as soon as the sheet is refreshed
- v-jingzhang
Every time when the query is refreshed, the custom column is re-evaluated so it generates the empty column again. You want the user input to be remained but the data from SharePoint online list to be refreshed?
Jing
Can you add the column to the original table, before hit hits Power Query, or create a second table for user input, then import into power query and merge queries...? or create a new blank query in power query, then merge....
- Syndicate_Admin
Yes, I had created an empty column in the Sharepoint list before importing it to the power query, but any data filled in that column is deleted when the data is refreshed!!!
the second option, creating a table, is not applicable, as according to the data coming from the Sharepoint, the user fill in that columnSo, when the user(s) input their data, is it saving to the original file? Is the sharepoint file in view only mode? Can other people other than you edit the file?
- Syndicate_Admin
I've found a way to approach this. See my logic (not code) below:
TableA = An extract of the Excel table containing the blank column in which you've put your notes or whatever (we'll refer to it as "Notes").
TableB = SharePoint or SQL database dataset that you're using to update TableA
TableC = The normal query in PowerQuery that typically refreshes your Excel table containing your Notes column. (You'll understand this by the end).
1. In PowerQuery, pull TableA (as a connection only - no output to an Excel worksheet).
- Keep the primary-key column(s) associated with the Notes column and also keep the actual Notes column. If you don't have a primary key, you'll have to manually create one in PowerQuery by adding a new column containing concatenated field values that won't change when TableB refreshes and overwrites TableA in Excel.
- Example: transaction ID or Date, Email Address, and Issue might combine to create a unique identifier.
2. Pull TableB in PowerQuery.
- Add a column onto TableB containing the same Primary Key that you made from TableA (if needed).
3. Join/merge TableA onto TableB using the primary key column you have or that you created in step 1.
- This is what becomes TableC.
- Move columns to where you need them on the final output table.
- Remove any columns associated with any custom Primary Key columns you created in the process.
4. Use TableC to overwrite the original Excel table.
It might be a bit of a pain. But, it's necessary if you want to keep your values manually filled into a blank column you created when the query refreshes.
