how to: Direct Query hierarchical (two level) sql query into Power BI

[OPS-MLTSD]

Hello,

I would like to know how I can direct query the sql code for the hierarchical (two level) sql query. I would like to import the sql code for the data output below to PowerBI, PLEASE NOTE I ALREADY KNOW HOW TO DIERCT QUERY A NORMAL SQL CODE INTO POWERBI SO I AM NOT LOOKING FOR INSTRUCTIONS ON THAT) 

I have already tried by pressing 'Get Data' and selecting 'SQL Server' and it only shows data for the top level (1). If anyone could please help me figure out how I can direct query my sql code so that both levels show on Power BI, that would be much appreciated! 

[v-juanli-msft]

Hi @OPS-MLTSD 

Do you refer to hierarchical data in sql server?

You could expand the tables under Navigator.

Or you can write sql queries under Advanced Options->SQL statement.

Best Regards

Maggie

[OPS-MLTSD]

this is what I mean. How do I import the query to PBI?

[OPS-MLTSD]

as you can see from the picture I posted above, there are two levels of data. Level one (makred as 1 in red) and level two (marked as 2 in red). When I import the query like how I normally do by going into 'Get data' and selecting 'SQL Server' and following the procedure, I only get data for the top row (marked in red). I want to know, how can I get import the query from SQL to Power BI so that I can see all the data

[v-juanli-msft]

Hi @OPS-MLTSD 

I can't find any official documents discussing about it.

Based on my knowledge, Power BI doesn't support "hierarchial data" to generate dynamically when data source is with direct query.

Could you try to paste the "hierarchical sql query" in the "SQL statement" under the "Advanced options"?

Or create calcualted columns in Power BI to create hierarchical columns?

Video:

How to create hierarchial with power bi

Best Regards

Maggie

[OPS-MLTSD]

because of privacy reasons I can't paste the query. Is there any other way?