- •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
Using Queries pane shortcuts 91
With this set of tools, a user can easily get relevant insights from queries with only a few clicks. This is useful when you have to make corrections once you have identified errors that cannot be quickly corrected at a data source level.
Using Queries pane shortcuts
The Power Query UI offers smooth data navigation. This recipe will help you to understand how to use the Queries pane on the left of the UI. Other than basic query navigation, it is possible to perform different actions by using some shortcuts.
Getting ready
For this recipe, you need to download the FactInternetSales CSV file into your local folder.
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. A window with a preview of the data will pop up; click on
Transform data.
3.Go to the Queries pane and right-click on the query:
Figure 3.37 – Queries pane shortcuts
92Data Exploration in Power Query
4.From this view, you can carry out the following actions:
a)Copy and Paste queries.
b)Delete queries.
c)Rename queries.
d)Enable load: This allows you to enable or disable a query to be loaded in the model/dataflow.
e)Include in report refresh: This allows you to include/exclude a query in a model refresh.
f)Duplicate queries.
g)Reference: This enables you to create a new query that uses the applied steps from the query it is referencing. It does not duplicate a query, and every change in the original query will be reflected in the one that is referencing it.
h)Move To Group: You can create groups and organize your queries in folders.
i)Move Up/Move Down queries.
j)Create Function: This allows to create functions on top of the selected query.
k)Convert to Parameter: This allows you to convert queries into parameters.
l)Advanced Editor: From this shortcut, you can access the Advanced Editor view of the selected query.
m)Properties…: You can access a Properties section where you can rename a query, add a description, and flag the properties you see in the following screenshot:
Figure 3.38 – Queries properties