Hieararchical querry

[Hiiraga99]

Héy guys i'm new here , hope you doing all good !

So i have a problem in powerBI that wont solve , i have 2 tables "Timesheet"and "Entity" , the timesheet table has different column , but lets stay focused on 2 of them "Entity Id" and "Status" (Status are between 1 and 5 )

1 : Non submitted 

2 : submitted

3 : validated 

4 : approuved

5 : refused 

Timesheet had a relation with entity (entity_id) and entity has a column "entity_name" or name of different market and its sub_market , and another column "parent_id" ; if the parent id = -1 it means that its the top market ,, the entity_id wich has -1 in parent_id are 1,2,3 and 4 , so i want to regroup all submarket of one market/time , (i tried path but i doesnt work since there are some values that doesnt exist in the parent column) 

The hint is whenever i -for exemple- in sql server select * from entity where entity_id = 745 , it will give me a number parent_id , if i put the same number in the select * from entity where entity_id = *new parent_id number that i got* it will go down and finish at -1 , hope u did understand me , if not i'm here to answer some questions 

[Jimmy801]

Hello @Hiiraga99 

I don't really get what you really needed (for example what the timesheet table has to do with the grouping).

Could you please post both tables and the requested output needed?

Jimmy

[Hiiraga99]

Here are the picture :

in the SSMS , u can see that the parent_id number i put in the "where entity_id = " give me another table and it'll go until it reaches "parent_id = -1" , my request is i want to group all the big markets (who have parent_id = -1 , there are 4 of them , like they are markets and the others are submarkets) 

ENtityTimesheet

[Jimmy801]

Hello @Hiiraga99 

sorry, but I'm still not able to get a thing. I don't understand what here a relationship to the timesheet-table has to do

in PowerQuery you can Select rows as follows

let
	Source = #table
	(
		{"ENTITY_ID","ENTITY_NAME","ENTITY_TYPE","PARENT_ID","ENTITY_CODE","ARCHIVID","MANAGER_ID"},
		{
			{"81","ALU","","150","","",""},	{"82","Test","","-1","","",""},	{"83","DELPHI","","30","","",""}
		}
	),
    ChangedType = Table.TransformColumnTypes(Source,{{"PARENT_ID", type number}}),
    FilteredRows = Table.SelectRows(ChangedType, each ([ENTITY_ID] = "82")and [PARENT_ID]=-1)
in
	FilteredRows

If you want need it in Power BI then just connect to the two tables, connect them and create a filter where you filter for -1 and Entity ID

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Hiiraga99]

thank you for you answer , ill try mybest to be specific !

U asked about the relation in timesheet , timesheet has the "status" column wich i used to get a measure "global score" , but thats not important since i have the score now , the score is used to see if a market is validated or non validated , since we want to see the validation or non validation of an entity we need the entity table , ill post a picture so u can see the big market i need : 

1 Project & run lead local 

2 Project & run lead offshore

3 transverse (you guessed it they all have -1 in parent_id wich mean thet dont have a parent they are the top market and those top market have sub markets ) 

what i want here is to group all big markets with their sub market and try my score in them to see if they are validated or not (using the measure i did), how do i find their sub market , its in SSMS picture i upload , For exemple "select * from entity where entity_id=785" itll give me all the column with a parent_id = 31 with an entity name like "It Departement", if the second time i do the "select * from entity where entity_id=31" it ll give me a parent_id = 2 with an entity name "Apps" and same thing until it will reach parent_id = -1 , entity name =  Project & run lead local 

Conclusion : 

Project & run lead local has a submarket wich is Apps and Apps has a submarket wich is IT DEPARTEMENT , i want to groupe all submarket and sub sub market of each entities who have parent_id = -1 (there are 4 of them the last is called Datacenter )

[Anonymous]

@Hiiraga99  You are able to use SQL code directly in Power Query.  When you set up the data source, in the window where you place in the Server and Database, there is a window under Advanced that will accept SQL.

[Hiiraga99]

Thank you for your answer , can you please screen since i have POwerbi in french 

thank you