Forum Discussion
How do I create a date hierarchy in a field?
Good morning, Fabric community!
I have a question:
when I import a table with date-type fields, the date hierarchy is only applied to one of the two fields I have. The data is in Spanish, and the date fields are "FECHA PEDIDO" and "F. Archivo".
In the settings, I have the "Automatic date/time for new files" option enabled.
Is there a way to create the hierarchy in the "F. Archivo" field?
I know that calculated columns can be created to extract the year, month, day, and quarter values, and then a hierarchy can be set up in the modeling view, but the table has over 5 million records, and I'm not sure if this would be the most performance-efficient approach.
I'm waiting for a Power BI pro to lend me a hand. Thanks in advance.
in Power Query make sure that both are marked as type Date.
NOTE: Auto Date Time is something you want to unlearn fast. There is absolutely no need for it (your hierarchy should come from you own Calendar table) and it adds a lot (A LOT) of extra storage requirement for no good reason. Better to disable that feature.
2 Replies
- lbendlin
Super User
in Power Query make sure that both are marked as type Date.
NOTE: Auto Date Time is something you want to unlearn fast. There is absolutely no need for it (your hierarchy should come from you own Calendar table) and it adds a lot (A LOT) of extra storage requirement for no good reason. Better to disable that feature.
Thanks for the tip about disabling the Auto Date Time option.
I have a calendar table called 'Calendario.' The most efficient option would be to join the tables using the 'fecha' and 'F. Archivo' fields, and then use the hierarchy from my calendar table, am I right?
It was just mere curiosity to know why it is applied to one, and not the other.
