Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
@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).
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.
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.
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:
Ugh ok. And what i meant to say is to repeat it in every new report.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |