Forum Discussion
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!
Hi Anonymous
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.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
9 Replies
samdthompsonMemorable 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.
- samdthompson
Anonymous , 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).
v-jingzhangCommunity Support
Hi Anonymous
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.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.