Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Missing columns from SQL server view

Hi community,  I'm new to MSPBI so would you be able to help me out please.    I've made a connection to one of our SQL Servers and pull in the defintion from a view.  But only a subset of the columns are showing; i.e. the view def. has 10 columns but PBI is only showing 3 of them.   Any ideas what I'm missing?

 

thanks

Mark

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    That's pretty odd, what are the data types of those other 7 columns?

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    What's going on when specifying all the columns in a select query when connecting to the view?

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    Hi Anonymous,

     

    Could you please share your details sql of view, have u tried alter view and execute details of view again. then connect to viev?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      hi some views work ok.   But this one and a few others of a similar defintion don't.   There's nothing unusual about them, just standard views on stadnard tables.

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago

        Anonymous

        Would it be ok by re-creating the views? Would the same view always doesn't work when re-import in Power BI?

        Is it possbile to post the View and underlying table creation DDL?

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      9 years ago
      DineshPriyankar wrote:

      Hi,

       

      Anyone has a solution for this? I started experiencing the same with May 2017.

       

      If you need a sample code, I have written a simple code to check this against AdventureWorks2014 database, see this: http://dinesql.blogspot.com/2017/06/power-bi-does-not-show-all-columns-in-sql-server-view.html

       

      I might have made a mistake or missing something, appreciate if someone can help me on this.

       

      Regards

       

      Dinesh

      DineshPriyankar

      What if you grant more premission to "Jack", say sysadmin, I'd guess it is lacking of permissiong to get the meta data information.

      • DineshPriyankar's avatar
        DineshPriyankar
        Frequent Visitor
        9 years ago

        Hi,

         

        Thanks for the reply. I made him as db_owner and no luck. Then tried again after making him as sysadmin, same result. Can it be related to database schema?

         

        Thanks