Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
crobaseball
Helper II
Helper II

can you create/program a date hierarchy in M/Power Query?

In order to create a date table, I have some code in M which I'm using.  The problem is that it's not creating an automatic hierarchy.  Here's my code: 

</>

let

Source = List.Dates(#date(2015, 01, 01),
              Duration.Days(Date.From(DateTime.LocalNow()) - #date(2015, 01, 01)),
              #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), type number),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), type number),
#"Inserted MonthNum" = Table.AddColumn(#"Inserted Quarter", "MonthNum", each Date.Month([Date]), type number),
#"Inserted MonthName" = Table.AddColumn(#"Inserted MonthNum", "MonthName", each Date.MonthName([Date]), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted MonthName", "Day", each Date.Day([Date]), type number)
in

#"Inserted Day"

</>

 

As you can see, I'm not only generating the date, but each of the individual date pieces.  What's not there is a data hierarchy.  I'm well aware of how to create a date hierarchy in the Data/Report view, but I don't want to have to create one for every viz,  Instead I'd like to program it in M.  Can't find this when I google it.  Thanks!

1 ACCEPTED SOLUTION

Hi @crobaseball 

 

You don't have to mark it as a date table. Marking it as a date table will remove the auto-generated date hierarchy in the model. You only need to make sure the Auto date/time option is turned on in the File Options (Options > Current File (or Global) > Data Load > Time Intelligence), then apply your M code date table query into the model. This will create an automatic hierarchy for the date column. 

 

Additionally, since you already add year/quarter/month columns with M codes, you can use these columns directly for drill up and drill down purposes. In the following image, I put the Year/Quarter/MonthNum columns in the Axis fields at the same time, thus the line chart can be drilled up and down. 

051304.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

9 REPLIES 9
samdthompson
Memorable Member
Memorable Member

@crobaseball , as long as its a pretty straightfoward calendar you dont even have to create all the columns. As long as the table is marked as a date table and the date column formatted as a date, the auto hierarchy will kick in. Just make sure its turned on (Options>>CurrentFile>>DataLoad>>TimeIntelligence).  

 

// if this is a solution please mark as such. Kudos always appreciated.

H'm, this isn't working.  I marked it as a date table, but it's starting at the day level and doesn't have a way to drill up.

 

crobaseball_0-1620681615565.png

 

Hi @crobaseball 

 

You don't have to mark it as a date table. Marking it as a date table will remove the auto-generated date hierarchy in the model. You only need to make sure the Auto date/time option is turned on in the File Options (Options > Current File (or Global) > Data Load > Time Intelligence), then apply your M code date table query into the model. This will create an automatic hierarchy for the date column. 

 

Additionally, since you already add year/quarter/month columns with M codes, you can use these columns directly for drill up and drill down purposes. In the following image, I put the Year/Quarter/MonthNum columns in the Axis fields at the same time, thus the line chart can be drilled up and down. 

051304.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

So if I *don't* mark it as a date table, then the hierarchy should automatically be created?  I'll have to try this.

Yes, it should be. I copied and pasted your M codes into a blank query and apply it into the model. It created a hierarchy for the date column automatically as shown in the screenshot of my previous reply.

i'll trust that you're right!

Hello, right click on the date and select create date heirarchy:

 

2021-05-11_9-25-28.jpg

// if this is a solution please mark as such. Kudos always appreciated.
crobaseball
Helper II
Helper II

Ugh ok.  And what i meant to say is to repeat it in every new report. 

samdthompson
Memorable Member
Memorable Member

Hello, you cant do this in Power query. In saying that you dont have to recreate for each viz in desktop either. Once you create a date hierarchy on your date table, its available for all visuals.

 

 

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.