Direct Query SQL table fails to import even though...

MarkHoughton · ‎07-06-2017

Hi, I am new to PowerBI and just finding my way around. I have imported using direct query a number of tables and pull through financial information ok. However, I am attempting to build a department heirarchy and failing. In my SQL database I have 2 tables, one with the dept id and dept description, the other table has two columns, one id is the matched dept id, the other id is its parent id. I have a sql script which pulls the names in so that i have 4 columns, Parent Name, Parent Id, ChildDept Id, ChildDept Name. All well and good and it displays ok in the query editor when attempting to import.

However, it will not allow me to 'accept' the query. I get this error.

I don't know why if it previews the source ok that it fails to 'accept' it.

What am i doing wrong ?

Thanks

Mark

Greg_Deckler · ‎07-06-2017

Can you post the SQL you are using?

One nit-pick, when you use Direct Query, you are technically not "importing" anything.

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

MarkHoughton · ‎07-06-2017

Hi,

I know it's not importing, but I don't know what other term to use and to my mind because I am merging 2 different tables then I feel like I am 'importing'. Anyway....

Here is the script

select Dept2.[MEM_DESC] as 'Parent Dept',Dept2.MEM_ID as 'Parent Id',Dept1.MEM_ID as 'Dept Id', Dept1.[MEM_DESC] as 'Child Dept' from [CLIVE].[CLIVE].[CUST1] Dept1 inner join [CLIVE].[CLIVE].[CUST1_TREE] DeptTree on Dept1.MEM_ID=DeptTree.MEM_ID inner join [CLIVE].[CLIVE].[CUST1] Dept2 on Dept2.MEM_ID=DeptTree.MEM_PID