Setting hierarchy for the columns in table.

[sathyaganapathi]

Hi all,

I have data in table  with Date and dept as shown below.   I want to set the hierarchy to the column to drill down data from year > month > Dept in power BI display.  Please help.

example Data table:

From:

InputDateTime	Dept
11/4/2022 9:52:00 PM	Route
11/4/2022 7:06:00 PM	Drilling
11/4/2022 3:07:00 PM	SM
11/4/2022 11:52:00 AM	SM
11/4/2022 7:53:00 AM	Copper
11/4/2022 7:53:00 AM	SM
11/3/2022 6:08:00 PM	Drilling
11/3/2022 2:08:00 PM	Copper
10/31/2022 8:05:00 PM	Drilling
10/31/2022 6:24:00 PM	Copper
10/31/2022 11:35:00 AM	Photo
10/31/2022 10:13:00 AM	SM
10/30/2022 10:41:00 PM	Drilling
10/30/2022 7:36:00 PM	Copper
10/30/2022 8:04:00 AM	Drilling
10/27/2022 10:31:00 PM	Copper
10/25/2022 9:46:00 PM	Drilling
10/24/2022 3:57:00 PM	Relam
10/24/2022 1:48:00 PM	Relam

To:

Year	Month	Dept
2022	January	Copper
2022	January	Drilling
2022	January	Lab
2022	January	Photo
2022	January	Relam
2022	January	Route
2022	January	SF
2022	January	SM
2022	January	Tech
2022	February	Copper
2022	February	Drilling
2022	February	Lab
2022	February	Legend
2022	February	PECAM
2022	February	Photo
2022	February	Relam
2022	February	SF
2022	February	SM
2022	March	Copper
2022	March	Drilling
2022	March	Lab
2022	March	Legend
2022	March	PECAM
2022	March	Photo
2022	March	Relam
2022	March	SM

[Dinesh_Suranga · Accepted solution]

@sathyaganapathi 

Hi

First change data type of your InputDateTime column to Date. Then you can see date hierarchy in InputDateTime column. Add year and month from that date hierarchy to table and then add dep.

If you have any question regarding this please reply to this massage.

Please accept this as the solution if this solved your issue.

Thank you.