Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
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
@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
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.
@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.
Regards,
Lydia
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
@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)?
Regards,
Lydia
@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.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.