cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kfarynowski Frequent Visitor
Frequent Visitor

Direct Query to SQL database - not all columns / data items returned

I'm using Direct query in PowerBI Desktop to SQL server database, with Direct Query Mode.   When the connection is made showing data preview to edit, only a few of the columns/data elements from the database show up.  When I looked at the native query, the code only has a couple of the columns in the select statement.   Why is direct query only selecting a few columns?  Below is the native query - but the table has many more columns.  It's not clear why these columns were selected by the engine - I only selected the table name. 

 

select [$Table].[DtFile] as [DtFile],
    [$Table].[Style] as [Style],
    [$Table].[StatusIDCur] as [StatusIDCur],
    [$Table].[DeletionCmmnt] as [DeletionCmmnt]
from [dbo].[ClkCaseHdr] as [$Table]

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

Re: Direct Query to SQL database - not all columns / data items returned

@kfarynowski,

I am not able to reproduce your issue, please review the following screenshots in my environment. Could you please share me DDL and DML of your table? I will test it in my Power BI Desktop.

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kfarynowski Frequent Visitor
Frequent Visitor

Re: Direct Query to SQL database - not all columns / data items returned

Cut/paste below for one of the tables.

 

Also to note, when I use the data connection option in excel of "from other sources / from sql server" where it asks for logon credentials, I get all the columns.  If I use the excel connection option of "new query / from database / from  SQL server database", I don't.     In PowerBI I don't get the option to put in logon credentials.  

 

USE [Justice]
GO

SELECT [CaseID]
      ,[CaseCategoryKey]
      ,[CaseUTypeID]
      ,[CaseSubTypeID]
      ,[CaseSecGrpID]
      ,[CaseStatClkCdID]
      ,[DtFile]
      ,[Style]
      ,[CaseAssignmentHistoryIDCur]
      ,[StatusIDCur]
      ,[AutoBuildCaseStyle]
      ,[CaseTypeComment]
      ,[UserIDCreate]
      ,[TimestampCreate]
      ,[UserIDChange]
      ,[TimestampChange]
      ,[PrevSecGrpID]
      ,[LeadFamilyCaseFlag]
      ,[CaseTypeChangeReasonID]
      ,[DeletionCmmnt]
  FROM [dbo].[ClkCaseHdr]
GO


USE [Justice]
GO

UPDATE [dbo].[ClkCaseHdr]
   SET [CaseID] = <CaseID, CaseID,>
      ,[CaseCategoryKey] = <CaseCategoryKey, CaseCategoryKey,>
      ,[CaseUTypeID] = <CaseUTypeID, CodeID,>
      ,[CaseSubTypeID] = <CaseSubTypeID, CaseSubTypeID,>
      ,[CaseSecGrpID] = <CaseSecGrpID, CodeID,>
      ,[CaseStatClkCdID] = <CaseStatClkCdID, CodeID,>
      ,[DtFile] = <DtFile, datetime,>
      ,[Style] = <Style, varchar(5000),>
      ,[CaseAssignmentHistoryIDCur] = <CaseAssignmentHistoryIDCur, CaseAssignmentHistoryID,>
      ,[StatusIDCur] = <StatusIDCur, int,>
      ,[AutoBuildCaseStyle] = <AutoBuildCaseStyle, Flag,>
      ,[CaseTypeComment] = <CaseTypeComment, Cmmnt,>
      ,[UserIDCreate] = <UserIDCreate, UserIDCreate,>
      ,[TimestampCreate] = <TimestampCreate, TimestampCreate,>
      ,[UserIDChange] = <UserIDChange, UserIDChange,>
      ,[TimestampChange] = <TimestampChange, TimestampChange,>
      ,[PrevSecGrpID] = <PrevSecGrpID, CodeID,>
      ,[LeadFamilyCaseFlag] = <LeadFamilyCaseFlag, Flag,>
      ,[CaseTypeChangeReasonID] = <CaseTypeChangeReasonID, CodeID,>
      ,[DeletionCmmnt] = <DeletionCmmnt, varchar(max),>
 WHERE <Search Conditions,,>
GO

 

 

******************

 

USE [Justice]
GO

/****** Object:  Table [dbo].[ClkCaseHdr]    Script Date: 10/2/2017 9:43:42 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ClkCaseHdr](
 [CaseID] [dbo].[CaseID] NOT NULL,
 [CaseCategoryKey] [dbo].[CaseCategoryKey] NOT NULL,
 [CaseUTypeID] [dbo].[CodeID] NULL,
 [CaseSubTypeID] [dbo].[CaseSubTypeID] NULL,
 [CaseSecGrpID] [dbo].[CodeID] NULL,
 [CaseStatClkCdID] [dbo].[CodeID] NOT NULL,
 [DtFile] [datetime] NULL,
 [Style] [varchar](5000) NULL,
 [CaseAssignmentHistoryIDCur] [dbo].[CaseAssignmentHistoryID] NOT NULL,
 [StatusIDCur] [int] NOT NULL,
 [AutoBuildCaseStyle] [dbo].[Flag] NOT NULL,
 [CaseTypeComment] [dbo].[Cmmnt] NULL,
 [UserIDCreate] [dbo].[UserIDCreate] NOT NULL,
 [TimestampCreate] [dbo].[TimestampCreate] NOT NULL,
 [UserIDChange] [dbo].[UserIDChange] NULL,
 [TimestampChange] [dbo].[TimestampChange] NULL,
 [PrevSecGrpID] [dbo].[CodeID] NULL,
 [LeadFamilyCaseFlag] [dbo].[Flag] NOT NULL,
 [CaseTypeChangeReasonID] [dbo].[CodeID] NULL,
 [DeletionCmmnt] [varchar](max) NULL,
 CONSTRAINT [PK_ClkCaseHdr] PRIMARY KEY NONCLUSTERED
(
 [CaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Moderator v-yuezhe-msft
Moderator

Re: Direct Query to SQL database - not all columns / data items returned


@kfarynowski wrote:

Cut/paste below for one of the tables.

 

Also to note, when I use the data connection option in excel of "from other sources / from sql server" where it asks for logon credentials, I get all the columns.  If I use the excel connection option of "new query / from database / from  SQL server database", I don't.     In PowerBI I don't get the option to put in logon credentials.  

 

 


@kfarynowski,

What logon credential do you refer to? Do you mean the credential to connect to SQL database? If so, before you connect to the SQL database, delete the permissions for the existing SQL data source under Global permissions in Power BI Desktop, then Power BI Desktop will prompt you to enter user and password.

After performing the above steps, please check if you get all columns.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kfarynowski Frequent Visitor
Frequent Visitor

Re: Direct Query to SQL database - not all columns / data items returned

Thanks.  I tried that, and while it does give me the option to put in credentials, I still only get a the same, few data items/columns.  I also found that in excel, if I select option that takes me to the editor  (Get and Transform , New Query), I only get the (same) limited columns.  If I  use "Get external data"  / sql server, I get all the fields but not the option to use the editor to do transformations.

Moderator v-yuezhe-msft
Moderator

Re: Direct Query to SQL database - not all columns / data items returned

@kfarynowski,

I am not able to re-create the table using your CREATE TABLE statement. I got the following error message when excuting the statement.
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kfarynowski Frequent Visitor
Frequent Visitor

Re: Direct Query to SQL database - not all columns / data items returned

It appears I am only getting the attributes with data type of int, varchar, char.  I am not getting any data for data types beginning with "dbo."   Is there something in the settings the governs that?  Or something that is changed in the query editor or when selection source?

 

In the query editor:

 

let
    Source = Sql.Database("odysseyprodrptdb\FLORANGEPRODRPT", "justice"),
    dbo_Addr = Source{[Schema="dbo",Item="Addr"]}[Data]
in
    dbo_Addr

Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Direct Query to SQL database - not all columns / data items returned

@kfarynowski,

You are using user defined data type. I test the similar scenario as  yours, everything works well in Power BI Desktop, please review the screenshots in my environment. Have you tried to connect to SQL database in the latest version of Power BI Desktop(2.51.4885.701)?
1.JPG2.JPG3.JPG


Regards,
Lydia

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