Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hieararchical querry

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 

 

General Comment
Need Help
Tips and Tricks

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

      thank you

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Anonymous 

         

        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