Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is there a way to add an empty column within a dataset imported from SharePoint online list for a user input
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).
2. Pull TableB in PowerQuery.
3. Join/merge TableA onto TableB using the primary key column you have or that you created in step 1.
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.
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....
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 column
So, 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?
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.
thanks for your reply, but any amount the user keyed in this null column got deleted as soon as the sheet is refreshed
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
exactly, i need user input to be sustained meanwhile, SP data to be refreshed
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.