Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
saujanyae
Helper II
Helper II

New Columns added not showing up in Direct Query Connection

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

19 REPLIES 19
sdeshmukh
Regular Visitor

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?

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
New Member

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
Advocate II
Advocate II

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
Not applicable

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
Community Support
Community Support

Hi @saujanyae

 

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

 

InkedAnnotation 2020-02-26 123201_LI.jpgInkedAnnotation 2020-02-26 123226_LI.jpg

 

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!

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.

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!

@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!

Hi @saujanyae ,

 

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

 

Annotation 2020-02-28 093106.pngAnnotation 2020-02-28 093239.png

 

Does any error show there?

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!
 

@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.

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:

 

Annotation 2020-03-03 164540.pngAnnotation 2020-03-03 164443.png

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

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?

@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?

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

 

thanks

saujanya

Hi  @saujanyae ,

 

Is the issue solved?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 

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. 

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.

Support Ticket.gif

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.