 |
| Color Palette Pro in action |
At the end of 2022, I wrote about my early attempts to use ChatGPT to support BI solution development. Given how quickly things matured and the more I learned, I figured it was worth updating how I am currently using generative AI at work, about 2.5 years later. This is all based on my personal experience only, and I am continually learning and testing new use cases.
(If you're wondering: yes, I used generative AI to help write this post—lightly. I drafted the outline with my points and examples that I track, then asked ChatGPT to help with formatting and flow.)
Where generative AI is helping
Power Query (M)
As it relates specifically to Power BI/Excel, I have had really good luck getting help with M, even very complex code. This is great because it can be challenging when you tread outside the many helpful wizards in Power Query.
SQL
AI is very well-trained on SQL, as well -- even more useful if you include the SQL dialect.
- My first "Wow" moment was when I iterated with it to design a schema, create a DDL, and then populate the tables for a metadata-driven solution that generated and executed dynamic SQL
- Parse
.sqlfiles to extract source dependencies and organize the results in a table with dependencies by file
Client communication
- Rewrite explanations for clarity, brevity, a non-technical audience, or as it would be discussed in a specific industry
- Predict the interests or concerns of a person in a meeting so you can pre-emptively address them, based on their title and the agenda
Documentation and planning
- Revise a long questionnaire I had written to recommend which questions were redundant or so closely related that they could be combined, then categorize and arrange the categories in the most logical flow
- Create a workshop agenda and guidance based on a variety of inputs, including documents I uploaded on the topic
💡 Tip: Many interfaces allow attaching files. I often save repeat instructions in text files—like recipes I can drag and drop to quickly describe a task. Microsoft 365 Copilot can even generate or modify Office documents.
Design and formatting
- Generate a color palette for a series of 7 different "status" field values I provide using Color Palette Pro GPT (see top of post for output)
- Convert a Word document I uploaded to wiki markup for fast publishing
- Format a messy SQL query or expression for readability
- Assist with standardizing formatting across documentation
- List the required dimensions and measures to build a design mockup image I upload
Task prep and problem solving
- Come up with questions to ask or inputs for a complex topic, like determining the TCO of a BI platform
- Come up with a way to find a replace values in a CSV that is too large to open (gave me a PowerShell script)
- Translate functions that don’t exist in a platform using functions that are available, e.g., Qlik's
ApplyMapinto M
💡 Tip: Metadata remains a key accelerator. I often use third-party tools, DMVs, or TMDL to "seed" AI inputs—like passing a list of measures and expressions to generate a data dictionary.
Where agentic and targeted AI-assisted tools are helping
This is an emerging area I'm paying more attention to—tools that take action, themselves, not just suggesting what to do in text format, which can leave a lot of work in tools with a GUI.
Specific to what I do
- Power BI Copilot: When using our proprietary conversion technology, not every DAX measure works on the first try. My first stop is the DAX Query pane and simply telling Copilot "Fix this measure".
- Copilot for GitHub in Visual Studio Code (and Cursor): I tested adding TMDL measure comments with business definitions based on the DAX and use case I described, but I can imagine many other scenarios where I will leverage this.
More universal use cases
- Voice-to-Text: Most Office apps and Windows itself (Win-H) have built-in voice-to-text, but I use Wispr Flow for much more accurate voice-to-text, as measured by typing what you really intend to say, like correcting yourself, mid-sentence. The built-in voice-to-text in ChatGPT is also quite good, but I only use that for prompting ChatGPT, not writing a document or Teams message.
Where generative AI has been less helpful
DAX
It is still not great with the type of DAX questions I need help with. This could be because the way DAX works depends on a lot of context that can be hard to provide: the way the data is modeled, relationship characteristics, the object in the front-end, different fields in the object, and filter interactions. (I have not compared to see whether Copilot within Power BI Desktop is better at this.) Still, it can be helpful if you, ex., want to understand the difference between two specific DAX functions.
Governance and admin in Power BI/Fabric
It is hard to keep track of the nuances of what exact combination of settings and licenses in the Power BI/Fabric service enable people to do the specific thing you would like, and generative AI struggles with this, as well... not that it answers any less confidently, of course. And Like most people, it’s still new to Fabric.