Direct Query SQL table fails to import even though it previews correctly

[MarkHoughton]

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]

Can you post the SQL you are using?

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

[MarkHoughton]

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

[v-caliao-msft]

@MarkHoughton,

Can you get expected result by using this query in SQL Server Management Studio?

See similar threads below: 

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table... 

http://community.powerbi.com/t5/Integrations-with-Files-and/Expression-Error-The-key-did-not-match-a... 

https://community.powerbi.com/t5/Service/Key-Expression-Error-The-key-didn-t-match-any-rows-in-the-t...

Regards,

Charlie Liao

[MarkHoughton]

Hi, yes, when i enter the query in SQL managament studio the tables are merged as I expect, it displays the same results as the BI preview.