Update a duplicate reference table with new columns?

[Anonymous]

I have a table from SharePoint 2016 with columns of test questions.  To be able to report on the test question columns, I made a reference copy of that table and in the new table, I unpivoted all the question columns so they are in one column.

Now I just added new questions to my SharePoint list but of course, those new questions are not being captured in my reference table. 

How do I update my original table to show the new question columns?  How can I update my reference table to show the new questions without deleting the table and createing a new reference table?

[v-juanli-msft]

Hi @Anonymous 

As tested, it can work on my side.

Could you show your example data?

I use the example data as below

question1	question2	question3	question4	question5
yes	no	yes	yes	no

I right-click on the table, select any option below to get a new table

1. copy, then paste the table to get a new table

2. click on "reference" to get a new table

3. click on "duplicate" to get a new table

unpivot all question columns by selecting all question columns and "Transform"->"Unpivot columns"

(don't select "unpivot only selected columns",

 In my test, i only have question columns.

 If you have more columns, some columns doesn't need to unpivot,

 please select the columns don't need to unpivot,  then select "unpivot other columns" )

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I was able to get it to work. I had to go into Advanced Editor and manually add the new columns on both tables. Challenging part was some of the column titles were very long and had a / in them so adding the full title didn't work. I had to create a new table with the new columns just to see how PBI renamed them.

Not a very user friendly I would say

[v-juanli-msft]

Hi @Anonymous 

When your data source add new data, just click on "refresh all" button in Power BI Edit Queries, the new data would add to the table.

You could unpivot all columns by adding an index column, selecting this index column,then select "unpivot other columns", so that new columns would be automatically perform "unpivot columns" function.

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

Best Regards

Maggie