Forum Discussion
Extract one column into new table
Hi,
This is an add one and I am well aware this is bad practice for the short-term there is nothing I can do about the data source.
Anyway..
What would be the best way to get just one column (of ID numbers) from a table and put them into another table?
Then, when there is a data-refresh, only append new ones, never overwrite.
A bit of context as this is hard to explain. I have a table of data and we need a new column (which is always human determined every few weeks) so someone will go into this new table every so often and put in comments to each of the ID numbers.
Layout for some detail
Tbl_Data - this table is live data and refreshed
has many columns:
ID
Data
Name etc etc etc
Tbl_New - this would be the new table and would include:
ID - from Tbl_Data and append new one on refresh
Human_comments - this is the column a human would enter for each ID (manually for now).
We can then relate the two tables to get the Human_comments field as required.
I know this probably makes little sense.
3 Replies
- Greg_Deckler
Community Champion
Anonymous Couple ways to do this. In Power Query, right-click your first query and choose Reference. Then select your column and "Remove other columns". You can also do this in DAX simply by using SELECTCOLUMNS, like:
Tbl_New = SELECTCOLUMNS('Tbl_Date',"ID",[ID])
Now, the "append new only", that's an incremental refresh thing and you won't be able to do that unless you meet the incremental refresh requirements. https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
- Greg_Deckler
Anonymous Well, there are ways but none of them are exactly pretty in my opinion. My first question would be why you feel you need to append versus just refresh the entire list each time. Are you concerned about performance or are you concerned that some values may "go away" later?
You can do an Append query, but, again, it isn't exactly pretty in my opinion. You would need to have a date column or something that you can key off of like an Index perhaps as being "current". You would construct your query as before using Reference. You would then filter for all dates up to a certain date or an Index up to a certain index. Then remove other columns. Now, you would construct a second query the same way, using Reference but this time enact the opposite filter, greater than a certain date or index. Remove other columns on this one as well. You would then create an Append query or otherwise append your queries together. You could then use a "remove duplicates" operation to get rid of any duplictaes if you care about that.
Not exactly a work of art but doable.
