In this writing, I want to share how I start to use TMDL View feature with GitHub Copilot in VSCode.
As a Power BI developer, staying ahead of new features that enhance productivity is crucial. One such advancement is the TMDL (Tabular Model Definition Language) View feature, now available in Power BI desktop. Additionally, integrating AI-powered tools like GitHub Copilot into Visual Studio Code (VSCode) can further streamline workflows. In this blog post, I will share my experience getting started with the TMDL View feature in VSCode while leveraging GitHub Copilot to enhance work-efficiency.
In this sample, I try two tasks.
In the semantic model, calendar dimension table is created by writing a DAX code, but it does not have quarter column. I want to recreate the calendar dimension table by rewriting the DAX code to have a quarter column.
In the semantic model, some of the relationships are showing bi-directional relationship. I want to find those and delete these bi-directional relationships.
I have a sample PBIP file, created using the sample data provided by Microsoft.
I performed several transformation steps in Power Query Editor, resulting in the following data model.
Recreate the calendar dimension
The calendar_dimension table was created using the following DAX code.
calendar_dimension =
ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( MIN ( financials_fact[Date] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( financials_fact[Date] ) ), 1, 1 )
),
"Year", YEAR ( [Date] ),
"Year-Month", FORMAT ( [Date], "yyyy-mmm" ),
"Year-Month sort", EOMONTH ( [Date], 0 ),
"Month name", FORMAT ( [Date], "mmmm" ),
"Month number", MONTH ( [Date] )
)
In TMDL View, drag and drop the calendar_dimension table, then rename the tab to "Calendar Dimension".
Save the file as a PBIP file.
Open VSCode and install the required extension.
In VSCode, search for the "calendar dimension.tmdl" file within the TMDLScript folder. The same TMDL script visible in Power BI Desktop is also available in VSCode.
Right-click and select Copilot -> Editor Inline Chat.
Request to add a new column (Quarter column) to the calendar_dimension table. When making the request, I provided as much detail as possible.
As shown below, a new line is added to the DAX code. Click Accept and save the change.
Reopen the PBIP file, where the modified script appears as shown below. Click Apply to apply the changes to the data model.
The calendar_dimension table now includes the newly added Quarter Number column.
Delete bi-directional relationships
Drag all relationships into the TMDL View. As shown below, the tab name is "Script 1".
Search for the "Script 1.tmdl" file in VSCode.
Right-click and select Copilot -> Editor Inline Chat, then enter the following request:
"Find crossfilteringbehavior and delete them if possible."
I tried to phrase the request clearly, intending to remove any bidirectional (crossfilteringbehavior) relationships.
However, as shown in the results below, the relationships themselves were not deleted. Instead, only the crossfilteringbehavior: bothDirections lines (line 4 and line 9 in the above figure) were removed.
It seems that the relationships were modified. 🤔🤔🤔
I clicked Accept, saved the changes, then reopened the PBIP file. In TMDL View, I clicked Apply to apply the modifications.
As a result, the relationships were changed to one-to-many relationships. My initial goal was to delete the relationships entirely, but only the lines in the script containing the word "crossfilteringbehavior" were removed. 🤔
Anyway, the relationships now display as one-to-many connections.
Perhaps my understanding of how the script defines relationships in the data model is incomplete, which may be why the relationships were modified instead of being deleted.
Summary
In this blog post, I explored how to leverage the TMDL View feature in VSCode alongside GitHub Copilot to enhance my Power BI development workflow. By integrating these tools, I was able to edit and manage tabular model definitions (TMDL files) more efficiently, bringing structured version control and AI-assisted coding into my Power BI projects.
Key Learnings
TMDL View in VSCode allows for a structured, text-based approach to managing Power BI datasets.
GitHub Copilot can assist in generating TMDL script, automating DAX code creation, and accelerating the development process.
While Copilot is powerful, clear and precise prompts are crucial to getting the intended results when modifying TMDL scripts.
Understanding how TMDL defines relationships is essential, as removing crossfilteringbehavior does not delete relationships but instead modifies them to default one-to-many configurations.
Next Steps & Further Research
Deeper Exploration of TMDL scripts: I want to further analyze how TMDL structures relationships, constraints, and metadata in Power BI datasets.
Optimizing AI-Powered Development: Experimenting with Copilot’s ability to assist in refactoring complex models and handling large-scale dataset transformations.
Potential Limitations of TMDL in Large Models: Investigating any scalability challenges and performance implications when working with extensive tabular models in VSCode.
I’m curious—have you tried TMDL View in your Power BI development workflow? What challenges or advantages have you encountered when integrating GitHub Copilot? I want to learn more from the community to share and refine best practices together! 🚀
I hope this makes it more enjoyable and engaging to start exploring and experimenting with the TMDL View feature in Power BI. Embracing this new approach can enhance your workflow, improve model management, and open up new possibilities. Happy exploring!
Comments