Query overview in Power BI Desktop
Power Query is data processing, data transforming, and data preparing the engine. It appears in a graphical representation to get the data of experts and Query Editor. It also applies alterations that perform in graphical representation.
The Power Query engine locates in products
and operations where data stored. It performs modification, selection, and
obligates the process of data.
To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training
How does Power Query
help by data recovery?
|
Present Application |
How PQ helps? |
|
❖
Data is tough to find |
It allows a wide range of connectivity to data experts,
shapes of data, and data views. |
|
⮚
Connectivity data is firmly fragmented |
The flexibility of knowledge and similarity of query
abilities across all data experts. |
|
⮚
We are reshaping the data before using |
Deeply automatic experience is rapidly and parallelly
builds queries to any data expert. |
|
⮚
Shaping not replicated |
It allows users to modify present queries using automatic
experience when it defines queries initially. |
|
⮚
Volume |
PQ can work upon a subset of the whole data set to
establish the required data conversions by allowing users to transform their
data into a flexible size. |
|
⮚
Velocity |
Queries can be stimulated manually or by managing schedule
refresh abilities in specific products |
|
⮚
Variety |
Over 350 different data varieties transformed and allow
users to work with data from an expert. |
Types of
views:
- Report views – wherever you employ queries
you produce to make compelling visualizations, organized as you wish them
to seem, and with multiple pages, that you will share with others
- Data views – see the knowledge of the
information in your data model format and able to add measures, produce
new columns, and manage relationships.
- Relationships views – prepares a graphical
illustration of the relationships established in your knowledge model and works
or modifies them.
Introduction
of Power Query Editor
❖
By Selecting the report view will indicate in
a yellow band in Power Query editor. Power Query connects to one or more data
origins, shapes to modify the data to your requirements, and then stores the
model on the Power BI desktop.
❖
With no data connections, PQ Editor performs as a blank pane that
means ready for data.
❖
When a query arises, it connects to the web data expert that
loads the information about the PQ editor's data. It helps to shape the data
connection for establishing:
⮚
In the ribbon - some buttons are
activated to communicated with query data.
⮚
In the left pane - some list of data
queries is available for selection, viewing, and shaping.
⮚
In the center pane - the selected data
query reveals for shaping.
⮚ In the settings pane - properties of the query and steps are listed.
❖
In PQ Editor on the top left corner, we can see some options
like File, Home, Transform, Add Column, and View all these come under the ribbon pane can see as below:
❖
On clicking 'New Source'
a drop-down window pops and shows you some options to load a type of data you
want to choose like Excel, SQL Server, OData feed, Blank Query...
as below:
❖
What does the Transform tab
do?
⮚
It makes to add and remove columns.
⮚
It transforms data types.
⮚
In PQ editor, transform makes split the columns.
⮚
It transforms other query tasks.
❖
Some options are revealed after clicking on Add Column, such as column
from examples, custom column, invoke custom column, index column, etc. Each of these has a
specific task as below:
⮚
Adding a column to the data
⮚
It formats a column data
⮚
It adds custom columns
❖
Some options are revealed after clicking on View, such as query settings,
formula bar, monospaced, show whitespace,
etc. as below:
Left
Queries pane:
After selecting a left pane query, it demonstrates several
active and name queries to present in the center for shaping and transforming
data.
Center
Data pane:
Here the active and name queries are displayed in the center
when you right-click on the product that reveals menu items and these menu
items are similar to ribbon tabs.
Right
Query Setting pane:
When you select on a query step, and that itself applies to
the data, these applied steps stored in query settings that show recent data
and score columns.
Saving
your query data in PQ Editor:
❖
Click on File top the
top left corner of the editor, and a window pops showing options like Close & Apply, Apply, Close, Save, etc. Click on save and name your data. If you made any changes in the editor, you
could either click on Save or
❖
Click on Close &
Apply that applies the changes in your record, and it closes the editor.
❖
Power BI Desktop or Power query can save your files in the “.pbix” file format.
PQ
experiences:
- It provides the PQ Editor's user interface.
- This interface aims to provide you with an
applied data transform that combines a set of ribbons, menu items, key
buttons, and some responsive parts.
- Power Query experience is in the initial state of
data preparation that allows users to connect the data sources.
- It applies thousands
of varieties of transformed data by revealing and sorting these
transformations.
PQ
Formula Language:
●
Some data transformations cannot work in the right way, as shown
in the graphical view.
●
At those times, we require simple operations, settings, and
transformations of data that this graphical report will not support it should
do as per the query scripts.
●
These query scripts use a scripting language in the background
for all data transformations called PQ
Formula Language, also known as M Language.
●
In the Power Query, the data transformation language is the M language. The programming part of the
query will be in the M language.
●
In PQ, using the "Advanced
Editor," it transforms the data in advance to modify query scripts.
●
Data Transformation and User Interface role not displayed to the
particular changes, you have to use the M code language and advanced editor for
tuning your data roles.
|
let Source =
Exchange.Contents("xyz@contoso.com"), Mail1 =
Source{[Name="Mail"]}[Data], #"Expanded Sender" =
Table.ExpandRecordColumn(Mail1, "Sender", {"Name"},
{"Name"}), #"Filtered Rows" =
Table.SelectRows(#"Expanded Sender", each ([HasAttachments] =
true)), #"Filtered Rows1" =
Table.SelectRows(#"Filtered Rows", each ([Subject] = "sample
files for email PQ test") and ([Folder Path] = "\Inbox\")), #"Removed Other Columns" =
Table.SelectColumns(#"Filtered Rows1",{"Attachments"}), #"Expanded Attachments" =
Table.ExpandTableColumn(#"Removed Other Columns",
"Attachments", {"Name", "AttachmentContent"},
{"Name", "AttachmentContent"}), #"Filtered Hidden Files1" =
Table.SelectRows(#"Expanded Attachments", each
[Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" =
Table.AddColumn(#"Filtered Hidden Files1", "Transform File
from Mail", each #"Transform File from
Mail"([AttachmentContent])), #"Removed Other Columns1" =
Table.SelectColumns(#"Invoke Custom Function1", {"Transform
File from Mail"}), #"Expanded Table Column1" =
Table.ExpandTableColumn(#"Removed Other Columns1", "Transform
File from Mail", Table.ColumnNames(#"Transform File from
Mail"(#"Sample File"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded
Table Column1",{{"Column1", type text}, {"Column2",
type text}, {"Column3", type text}, {"Column4", type
text}, {"Column5", type text}, {"Column6", type text},
{"Column7", type text}, {"Column8", type text},
{"Column9", type text}, {"Column10", type text}}) in #"Changed Type" |
Uses of
Power query:
●
It makes us connect types of sources like database, files,
social media, etc. for managing the data
●
Power query brings and combines the data to append, merge, and
join.
●
Determine new columns of data.
●
It either formats or removes the data.
●
Reshapes the data means it transposes or pivoting, un-pivoting.
●
Its records specification for manipulating the data.
●
Datasets exist in publish and restoration.
Conclusion:
This article will help you learn work with PQ
editor data, connect to data process, transform data, shape the data, connect
with different views. We also know the experiences and uses of Power Query
(PQ). To learn more about this article, follow to Power BI
Desktop – Power Query.
Comments
Post a Comment