Every time I use a date table in a new Power BI model (so… every time), I have to specify sort order for text columns – e.g. to sort January and February text fields by their month start instead of alphabetically.
This is something AI can do for us in bulk with TMDL! All we need to do is drop our date table TMDL into the chat, and ask it to set add a sortByColumn property for each text column for its twin date or relative number column. We can ask it to adjust date formatting there as well.
If you’re not sure what TMDL is or how to use it, I did a video on TMDL here – this will show you where to copy the code and re-run it to update the model. The video is about field descriptions, but the same applies to table columns (just pull the table TMDL instead of measure TMDL):
Just copy and paste that text out of the window and into an AI tool with a prompt.
I’m using Brian Grant’s date table here, so AI had no problem finding the “associated” date or numeric columns for the given text columns – they follow a particular naming pattern. As long as your column names are somewhat logical AI should be able to “get the idea” as far which fields should be sorted by what.
Model Matters
I tried this in GPT o3-mini high (the one that is “good at coding and logic”) first, and it was able to complete the task successfully without errors – adding about 10 alternate sorts for me. Claude 3.7 Sonnet handled it well also; here’s the prompt I used:
Here is my date table TMDL for Power BI. Add a line to each TEXT type column for sortByColumn, giving it a reference to the equivalent numeric or date type column. Do not omit or change the Power Query text in your response.
(paste date table TMDL here by dragging into the TMDL window and copy/pasting the text)This took a few variations to nail down – the second sentence about not changing the Power Query code or leaving it out is important. Even the high-end models had a tendency to omit that code or make up their own date table there if given a chance (probably because it’s long, and they’re trying to conserve resources).
Conversely, the free GPT model du jour left half of my date table columns out of its response, thus errored out. The associations it chose were correct, it just couldn’t handle the size of the text.
Rerunning it and asking it to include all the original code, it added a couple of its own columns and duplicated existing lineage tag GUIDs, causing it to error out again. After a few more tries, I couldn’t get the free GPT model to complete the task. My date table is fairly wide – I expect it might work if you don’t have a lot of columns on your date table.
Formatting
While we’re at it, we may as well throw a line into the prompt to format the date columns for us (instead of the default “Sunday, April 1, 2025”, have it display “4/1/2025”). The long date format default has been one of those pet peeves for me – having to change it every single time is annoying (Microsoft: I’m willing to buy a setting where you can choose your default date format!).
Here’s the same prompt, with added formatting specifications. All of the “advanced” models handled the additional ask without issue.
Prompt:
Here is my date table TMDL for Power BI. Add a line to each TEXT type column for sortByColumn, giving it a reference to the equivalent numeric or date type column. Do not omit or change the Power Query text in your response.
Also format all date-type fields by adding a line like this to the date column TMDL:
formatString: m/d/yyyy
(paste date table TMDL here by dragging into the TMDL window and copy/pasting the text)Here you can see the months are sorting and the formatting has been changed:
That’s lovely! Once you get the hang of it, the whole process only takes a few seconds. If you enjoy using TMDL with AI, I have another video about how to use it to write a whole set of measures at once.