- •Contributors
- •Table of Contents
- •Preface
- •Technical requirements
- •Installing a Power BI gateway
- •Getting ready
- •How it works
- •Authentication to data sources
- •Getting ready
- •How it works
- •Main challenges that Power Query solves
- •Getting ready
- •Technical requirements
- •Getting data and connector navigation
- •Getting ready
- •Creating a query from files
- •Getting ready
- •How it works...
- •Creating a query from a folder
- •Getting ready
- •How it works...
- •Creating a query from a database
- •Getting ready
- •How it works...
- •Creating a query from a website
- •Getting ready
- •How it works...
- •Technical requirements
- •Exploring Power Query Editor
- •Getting ready
- •Managing columns
- •Getting ready
- •Using data profiling tools
- •Getting ready
- •Using Queries pane shortcuts
- •Getting ready
- •Using Query Settings pane shortcuts
- •Getting ready
- •Using Schema view and Diagram view
- •Getting ready
- •Technical requirements
- •Formatting data types
- •Getting ready
- •Using first rows as headers
- •Getting ready
- •Grouping data
- •Getting ready
- •Unpivoting and pivoting columns
- •Getting ready
- •Filling empty rows
- •Getting ready
- •Splitting columns
- •Getting ready
- •Extracting data
- •Getting ready
- •Parsing JSON or XML
- •Getting ready
- •Exploring artificial intelligence insights
- •Getting ready
- •Technical requirements
- •Merging queries
- •Getting ready
- •Joining methods
- •Getting ready
- •Appending queries
- •Getting ready
- •Combining multiple files
- •Getting ready
- •Using the Query Dependencies view
- •Getting ready
- •Technical requirements
- •Setting up parameters
- •Getting ready
- •Filtering with parameters
- •Getting ready
- •Folding queries
- •Getting ready
- •Leveraging incremental refresh and folding
- •Getting ready
- •Disabling query load
- •Getting ready
- •Technical requirements
- •Using M syntax and the Advanced Editor
- •Getting ready
- •Using M and DAX – differences
- •Getting ready
- •Using M on existing queries
- •Getting ready
- •Writing queries with M
- •Getting ready
- •Creating tables in M
- •Getting ready
- •Leveraging M – tips and tricks
- •Getting ready
- •Technical requirements
- •Adding columns from examples
- •Getting ready
- •Adding conditional columns
- •Getting ready
- •Adding custom columns
- •Getting ready
- •Invoking custom functions
- •Getting ready
- •Clustering values
- •Getting ready
- •Technical requirements
- •Using Power BI dataflows
- •Getting ready
- •Centralizing ETL with dataflows
- •Getting ready
- •Building dataflows with Power BI Premium capabilities
- •Getting ready
- •Understanding dataflow best practices
- •Getting ready
- •Technical requirements
- •Exploring diagnostics options
- •Getting ready
- •Managing a diagnostics session
- •Getting ready
- •Designing a report with diagnostics results
- •Getting ready
- •There's more…
- •Using Diagnose as a Power Query step
- •Getting ready
- •Other Books You May Enjoy
- •Index
354 Implementing Query Diagnostics
The minimum requirements for installation are the following:
•.NET Framework 4.6 (Gateway release August 2019 and earlier)
•.NET Framework 4.7.2 (Gateway release September 2019 and later)
•A 64-bit version of Windows 8 or a 64-bit version of Windows Server 2012 R2 with current TLS 1.2 and cipher suites
•4 GB disk space for performance monitoring logs
You can find the data resources referred to in this chapter at https://github.com/ PacktPublishing/Power-Query-Cookbook/tree/main/Chapter10.
Exploring diagnostics options
You can use the Query Diagnostics tool to carry out an assessment of your queries and the steps performed. This means that once you have created Power Query steps, you can initiate a session that records and analyzes all the steps performed before you end that session. You can record what happens at the query level or deep dive at the single-step level. In this recipe, we will see where to find the Query Diagnostics option and how to set up the environment before starting a session.
Getting ready
For this recipe, you need to download the FactInternetSales CSV file. In this example, we will refer to the C:\Data folder.
How to do it…
Once you open your Power BI Desktop application, you are ready to perform the following steps:
1.Click on Get data and select the Text/CSV connector.
2.Browse to your local folder where you downloaded the FactInternetSales CSV file and open it. The following window with a preview of the data will pop up. Click on Transform Data:
Exploring diagnostics options 355
Figure 10.1 – Data preview
3.Browse to the Tools tab and you will see the different tools you can use to start a diagnostic session.
Figure 10.2 – Tools tab
You have the chance to diagnose a single step or start a general session and trace different actions, such as query refresh or the creation of a new step.
356Implementing Query Diagnostics
4.Before using this tool, we need to check the Diagnostic Options section. Click on the Diagnostic Options button, the last one in the Tools tab, and you will see the Options window pop up on the Diagnostics tab.
Figure 10.3 – Diagnostics tab in Options
Exploring diagnostics options 357
5.Scroll down to the end and focus on the Query Diagnostics section, as shown in the following screenshot:
Figure 10.4 – Query Diagnostics in Options detail
You can enable diagnostics at the report and Query Editor levels, but this could require you to have admin rights. If you cannot run it, enable only Query Editor, as shown in the preceding screenshot.
You can select a diagnostics level and select which type of output to get:
•Aggregated: You will easily understand diagnostics information because the information will be grouped allowing you to take immediate action.
•Detailed: All diagnostics information is shown at the highest level of detail.
You will also have the chance to select an Additional Diagnostics option, such as Performance counters (including resource consumption and information about CPU and memory) and Data privacy partitions (logical partitions used to isolate steps for data privacy).
From here, you can define which type of information you want to see and analyze once you run the diagnostics session. Remember to check the information before using this tool.