cancel
Showing results for 
Search instead for 
Did you mean: 
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
Solution Sage
Solution Sage

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

View solution in original post

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
Solution Sage
Solution Sage

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors