Streamlining Insights with Microsoft Copilot

Dive deeper into the world of AI innovation and stay ahead of the AI curve! Subscribe to our AI_Distilled newsletter for the latest insights. Don't miss out – sign up today!

This article is an excerpt from the book, Data Cleaning with Power BI, by Gus Frazer. Unlock the full potential of your data by mastering the art of cleaning, preparing, and transforming data with Power BI for smarter insights and data visualizations

Introduction

For those who have never heard of Microsoft  Copilot, it is a new technology that Microsoft has released across a number of its platforms that combines generative AI with your data to enhance productivity. Copilot for Power BI harnesses cutting-edge generative AI alongside your dataset, revolutionizing the process of uncovering and disseminating insights with unprecedented speed. Seamlessly integrated into your workflow, Copilot offers an array of functionalities aimed at streamlining your reporting experience.

When it comes to report creation, Copilot streamlines the process by allowing users to effortlessly generate reports by articulating the insights they seek or posing questions regarding their dataset using NLP. Copilot then analyzes the data, pulling together relevant information to craft visually striking reports, thereby transforming raw data into actionable insights instantaneously. Moreover, Copilot has the ability to read your data and suggest the best position to begin your analysis, which can then be tailored to suit the direction you want to take the analysis in.

This is great, but how can it help you clean and prepare data for analysis? Well, Copilot can be leveraged on multiple data tools from within the Microsoft  Fabric platform. For those who are not aware, Power BI has now become part of the Fabric platform. Depending on what type of license you have for Power BI, you might already have access to this. Any customers with Premium capacity licensing for the Power BI service would have automatically been given access to Microsoft  Fabric, and more importantly, Copilot.

That being said, currently, Copilot has only been made available to customers with a P1 (or above) Premium capacity or a Fabric license of F64 (or above), which is the equivalent licensing available directly from the Azure portal.

If you would like to follow along with the next example, you will need to set up a Fabric capacity within your Azure portal. Don’t worry, you can pause this service when it’s not being used to ensure you are only charged for the time you’re using it. Alternatively, follow the steps to see the outcome:

1. Log in to the Azure portal that you set up in the previous section of this chapter.

2. Select the search bar at the top of the page and type in Microsoft Fabric. Select the service in the menu that appears below the search bar, which should take you to the page where you can manage your capacities.

3. Select Create a Fabric capacity. Note that you will need to use an organizational account in order to create a Fabric capacity as opposed to a personal account. You can sign up for a Microsoft  Fabric trial for your organization within the window. Further details on how to do this are provided here: https://learn.microsoft.com/en-us/power-bi/ enterprise/service-admin-signing-up-for-power-bi-with-a-newoffice-365-trial.

4. Select the subscription and resource group you would like to use for this Fabric capacity.

5. Then, under capacity details, you can enter your capacity name. In this example, you can call it cleaningdata.

6. The Region field should populate with the region of your tenant, but you can change this if you like. However, this may have implications on performance, which it should warn you about with a message.

7. Set the capacity to F64.

8. Then, click on select Review + create.

9. Review the terms and then click on Create, which will begin the deployment of your capacity.

10. Once deployed, select Go to resource to view your Fabric capacity. Take note that this will be active once deployed. Make sure to return here aft er testing to pause or delete your Fabric capacity to prevent yourself from getting charged for this service.

Now you will need to ensure you have activated the Copilot settings from within your Fabric capacity. To do this, go to https://app.powerbi.com/admin-portal/ to log in and access the admin portal.

Important tip

If you can’t see the Tenant settings tab, then you will need to ensure you have been set up as an admin within your Microsoft  365 admin center. If you have just created a new account, then you will need to set this up. Follow the next links to assign roles:

• https://learn.microsoft.com/en-us/microsoft-365/admin/addusers/assign-admin-roles

• https://learn.microsoft.com/en-us/fabric/admin/microsoftfabric-admin

11. Scroll to the  bottom of Tenant settings until you see the Copilot and Azure OpenAI service (preview) section as shown:

Figure  – The tenant settings from within Power BI

12. Ensure both settings are set to Enabled and then click on Apply.

Now that you have created your Fabric capacity, let’s jump into an example of how we can use Copilot to help with the cleaning of data. As we have created a new capacity, you will have to create a new workspace that uses this new capacity:

1. Navigate back to Workspaces using the left navigation bar. Then, select New Workspace.

2. Name your workspace CleaningData(Copilot), then select the dropdown for advanced configuration settings.

3. Ensure you have selected Fabric capacity in the license mode, which in turn will have selected your capacity below, and then select Apply. You have now created your capacity!

4. Now let’s use Fabric to create a new dataflow using the latest update of Datafl ow Gen2. Select New from within the workspace and then select More options.

5. This will navigate you to a page with all the possible actions to create items within your Fabric workspace. Under Data Factory, select Datafl ow Gen2.

6. This will load a Datafl ow Gen2 instance called Datafl ow 1. On the top row, you should now see the Copilot logo within the Home ribbon as highlighted:

Figure – The ribbon within a Dataflow Gen2 instance

7. Select Copilot to open the Copilot window on the right-hand side of the page. As you have not connected to any data, it will prompt you to select get data.

8. Select Text/CSV and then enter the following into the File path or URL box:

https://raw.githubusercontent.com/PacktPublishing/Data-Cleaningwith-Power-BI/main/Retail%20Store%20Sales%20Data.csv

9. Leave the rest of the settings as their defaults and click on Next.

10. This will then open a preview of the file data. Click on Create to load this data into your Datafl ow Gen2 instance. You will see that the Copilot window will have now changed to prompt you as to what you would like to do (if it hasn’t, then simply close the Copilot window and reopen):

Figure – Data loaded into Dataflow Gen2

11. In this example, we can see that the data includes a column called Order Date but we don’t have a fi eld for the fi scal year. Enter the following prompt to ask Copilot to help with the transformation:

There's a column in the data named Order Date, which shows when an order was placed. However, I need to create a new column from this that shows the Fiscal Year. Can you extract the year from the date and call this Fiscal Year? Set this new column to type number also.

12. Proceed using the arrow key or press Enter. Copilot will then begin working on your request. As you will see in the resulting output, the model has added a function (or step) called Custom to the query that we had selected.

13. Scroll to the far side and you will see that this has added a new column called Fiscal Year.

14. Now add the following prompt to narrow down our data and press Enter:

Can you now remove all columns leaving me with just Order ID, Order Date, Fiscal year, category, and Sales?

15. This will then add another function or step called Choose columns. Finally, add the following prompt to aggregate this data and press Enter:

Can you now group this data by Category, Fiscal year, and aggregated by Sum of Sales?

As you can see, Copilot has now added another function called Custom 1 to the applied steps in this query, resulting in this table:

Figure – The results from asking Copilot to transform the data

To view the M query that  Copilot has added, select Advanced editor, which will show the functions that Copilot has added for you:

Figure – The resulting M query created by Copilot to carry out the request transformations to clean the data

In this example, you explored the new technologies available with Copilot and how they help to transform the data using tools such as Datafl ow Gen2.

While it’s great to understand the amazing possibilities AI brings to data, it’s also crucially important that you understand the challenges it presents.

Conclusion

In conclusion, Microsoft Copilot offers a groundbreaking approach to enhancing productivity and efficiency in data analysis and report generation within Power BI. By seamlessly integrating generative AI technology, Copilot revolutionizes the way insights are discovered and data is prepared, providing users with unprecedented speed and accuracy. Whether streamlining report creation or optimizing data management tasks, Copilot empowers users to unlock the full potential of their data, paving the way for more informed decision-making and actionable insights.

Author Bio

Gus Frazer is a seasoned Analytics Consultant focused on Business Intelligence solutions. With over 7 years of experience working for the two market-leading platforms, Power BI & Tableau, has amassed a wealth of knowledge and expertise. Gus has helped hundreds of customers to drive their digital and data transformations, scope data requirements, drive actionable insights, and most important of all, cleanse data ready for analysis. Most recently helping to set up, organize and run the Power BI UK community at Microsoft. He holds 6 Azure and Power BI certifications, including the PL-300 and DP-500 certifications. In this book, Gus offers readers invaluable guidance on ingesting, preparing, and cleansing data for analysis in Power BI. --This text refers to an out of print or unavailable edition of this title.