New Columns added not showing up in Direct Query Connection

[saujanyae]

I've used a direct query connection to build some charts and I have unpivoted a few columns. However, after new columns were added to the database view, I can't see them. I am not sure what I'm missing here.

When I create a new direct query connection, I'm able see all the columns. 

Need help 😞

Thanks

saujanya

[chouyueh]

Found a solution for this problem, for other reader clarification, this is related to connecting to a semantic model from another pbix with direct query. You will not see any queries under Edit Query or Advanced Editor as this is not direct query to a query only, the entire data model comes from semantic model. 

What i did was open Data Source Setting under Transform Data, click "Change source", re-select the semantic model again (the similar one) then wait the pop up box to load (Do not re-select any tables here otherwise it will be duplicated) and just click submit. The newly added fields/column will appear.

[sdeshmukh]

I am facing the same issue. I updated tablequery in database that returns one additional column but that column didn't appear in powerBI refresh. How can I resolve it?

[Lachhaman]

I came across this issue today and a search led me to this thread. I tried to delete my view and add it back. But frequent changes frustrated me leading again to do some trial and error. Here is what worked for me.

1. Select the table/view from Fields Panel, Right click on it

2. Select Edit Query which will take you to Power Query Editor 

3. Select the Home->Manage Columns->Choose Columns drop down and select Choose Columns

4. Just unselect any random column from the list and click Home->Apply & Close->Apply

5. This will show the all the columns now excluding the one unselected in Step 4

6. Repeat Step 3 and select all columns you need and repeat Step 4

Hope it works for you, it did for me. This obviously a bug in Power BI.

Thanks,

Lachhaman

[daniel_jansen]

You can go into the query edit, refresh the preview and then sort one of the new columns not shown ascending or descending and finally save and apply. They should appear now... and you can go back in and delete the sorting step in the query editor... the columns remain visible...

[aggregator3000]

Hi @saujanyae 
We had the same issue just now
We were simply loading the DQ SQL table without any PQ-level alterations; new columns added in SSMS/SQL table would not appear.
Fixed via forcing a table reload; added a sorting step in PQ -> table reloads in PBI -> Fields now appearing
Hope this helps

[Anonymous]

I have the same issue. Have you found a fix? 

Basically, I can't see the column in the fields of the direct query tables. However, I can see the new columns when I press Edit Queries > and press the DQ table (preview). 

[v-kelly-msft]

Hi @saujanyae ，

Have you clicked on "refresh" in report view or in query editor after you updated your data?

Based on my test,it works fine here,after I added new data to my database,then clicked "refresh",the data then was updated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[saujanyae]

Hello @v-kelly-msft 

Yes, I hit refresh and still didn't work. Was your test successful after unpivoting selected columns. I am guessing the new columns don't show up after transformations have been made.

[v-kelly-msft]

Hi @saujanyae ,

Yes,I followed your steps and unpivot some columns,then add new data in database, then refresh in desktop, after that,I create a table visual, and see the new data show up in the visual. But when I go to query editor,at the very start,I didnt  see the new data show up, so I refresh again ,and finally  it show up.

I'm guessing whether there's an error in the connection with your data source,would you pls go to query editor>advanced editor to see whether there's an error?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[saujanyae]

@v-kelly-msft when you say you added new data? did you add new rows? I added new columns in the database view after unpivoting existing columns and when I refreshed, I didn't see the new columns. I'll check advanced editor and come back to you.

Thanks for your prompt reply. Truly appreciate it!

[v-kelly-msft]

Hi @saujanyae ,

Yes, I added new rows,just now I tested to add new columns and return an error as below:

Does any error show there?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[saujanyae]

@v-kelly-msft  this error is because the columns must have the same scale and precision before you unpivot. Once you set that, this error will go away. My error is after you unpivot existing columns and then add new columns to the direct connection.

[v-kelly-msft]

Hi @saujanyae ，

Go to "edit queries">click on "advanced editor" ,to see whether the new column has been written inside:

Here I added new column,calling "Syear",at first it wasnt inside and returned an error,then I added it inside,now it works fine,as you see below:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[saujanyae]

Hello @v-kelly-msft , Kelly 

I don't want to add the new columns to the existing unpivioted columns and just show them as new columns. So in your example, you unpivoted the columns in PowerBI, added new columns to the database, and updated the unpivoted list. Did the new columns show up as is after refresh?

[saujanyae]

@v-kelly-msft I'm going to add new columns today and I've completed making my charts and a little concerned the new columns might not show up. I will check advanced editor and come back to you. However, adding lots of fields manually is a pain. It has to be simplier than this right?

[saujanyae]

@v-kelly-msft I'll add to Advanced Editor and come back to you.

thanks

saujanya

[v-kelly-msft]

Hi  @saujanyae ,

Is the issue solved?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[saujanyae]

hello @v-kelly-msft 

this issue is not resolved. I added new columns in the database view and looked at DQ preview, I don't see the new columns. I don't have to unpivot these new columns and just have them show up normally. 

[v-kelly-msft]

Hi @saujanyae ，

I cant find the reason about your issue,but I believe it should be an individual phenomenon,so better create a support ticket via below link:

https://powerbi.microsoft.com/en-us/support/

Here is a reference.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

I don't work a ton with Direct Query, can you paste your M code from Advanced Editor?