Get data - Module 1,2

Question 1

Flat file location

Answer 1

Local, OneDrive for Buisness and Personal, SharePoint - TeamSites

Question 2

Difference between One Drive Business and Personal

Answer 2

personal OneDrive account get many of the same benefits that you would with OneDrive for Business. However, you’ll need to sign in with your personal OneDrive account, and select the Keep me signed in option.

Question 3

Difference between One Drive and SharePoint

Answer 3

Saving your Power BI Desktop files to SharePoint Team Sites is similar to saving to OneDrive for Business. The main difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.

Question 4

How to change the source

Answer 4

Data source settings, Query setting, Advanced Editor

Question 5

How to get data from Relational Data Sources

Answer 5

https://learn.microsoft.com/en-us/training/modules/get-data/3-relational-data

Question 6

What is a Parameter

Answer 6

A parameter serves as a way to easily store and manage a value that can be reused. Parameters give you the flexibility to dynamically change the output of your queries depending on their value, and can be used for: 1. Changing the argument values for particular transforms and data source functions. 2. Inputs in custom functions.

Question 7

What is a Function

Answer 7

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order or structure.

Question 8

How to create Dynamic Reports with single value

Answer 8

https://learn.microsoft.com/en-us/training/modules/get-data/3b-parameters

Question 9

How to create Dynamic Reports with multiple value

Answer 9

https://learn.microsoft.com/en-us/training/modules/get-data/3b-parameters

Question 10

How to import NoSQL database

Answer 10

https://learn.microsoft.com/en-us/training/modules/get-data/4-nosql-database

Question 11

Type of Storage Mode and Definition

Answer 11

Import, DirectQuery, Dual - https://learn.microsoft.com/en-us/training/modules/get-data/6-storage-mode

Question 12

Differences between Azure Analysis Services and SQL Server

Answer 12

Analysis Services models have calculations already created. If you don’t need an entire table, you can query the data directly. Instead of using Transact-SQL (T-SQL) to query the data, like you would in SQL Server, you can use multi-dimensional expressions (MDX) or data analysis expressions (DAX).

Question 13

what is Connect Live in Azure Analysis Service

Answer 13

Connect live is an option for Azure Analysis Services. Azure Analysis Services uses the tabular model and DAX to build calculations, similar to Power BI. These models are compatible with one another. Using the Connect live option helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI. Azure Analysis Services can have a fast refresh schedule, which means that when data is refreshed in the service, Power BI reports will immediately be updated, without the need to initiate a Power BI refresh schedule. This process can improve the timeliness of the data in your report.

Question 14

What is Query Folding

Answer 14

Query folding is the process by which the transformations and edits that you make in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you’re actively making transformations. The reason for implementing this process is to ensure that these transformations can take place in the original data source server and don’t overwhelm Power BI computing resources.

Question 15

Benefits of Query Folding

Answer 15

More efficiency in data refreshes and incremental refreshes. Automatic compatibility with DirectQuery and Dual storage modes.

Question 16

How to know Query Folding is enable

Answer 16

If the View Native Query option isn’t available (not displayed in bold type), then query folding isn’t possible for this step, and you’ll have to work backward in the Applied Steps area until you reach the step in which View Native Query is available (displays in bold type). This process will reveal the native query that is used to transform the dataset.

Question 17

Native queries aren’t possible for the following transformations:

Answer 17

Adding an index column. Merging and appending columns of different tables with two different sources. Changing the data type of a column

Question 18

Power BI Query Error: Timeout expired

Answer 18

This error indicates that you’ve pulled too much data according to your organization’s policies. Administrators incorporate this policy to avoid slowing down a different application or suite of applications that might also be using that database. You can resolve this error by pulling fewer columns or rows from a single table. While you’re writing SQL statements, it might be a common practice to include groupings and aggregations. You can also join multiple tables in a single SQL statement.

Question 19

What is Power Query Editor

Answer 19

Power Query Editor in Power BI Desktop allows you to shape (transform) your imported data. You can accomplish actions such as renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, and much more. It is important to shape your data to ensure that it meets your needs and is suitable for use in reports.

Question 20

Difference between Pivot and Unpivot

Answer 20

Pivot aggregates or reshape data based on one or more columns, creating new columns for each unique value in those columns. This is often done to summarize or consolidate data.. Unpivot reshape data so that you have fewer columns and more rows, making it easier to analyze and work with.

Question 21

Difference between Transpose and Unpivot

Answer 21

In Transpose, Each row in the original data becomes a column in the transposed data, and each column becomes a row. This operation is useful when you want to present data in a different layout for analysis or reporting. Unpivoting is often used when you have data with multiple columns representing different attributes or dimensions, and you want to consolidate them into a single column while retaining their values.

Question 22

How to combine multiple tables into a single table

Answer 22

https://learn.microsoft.com/en-us/training/modules/clean-data-power-bi/5-combine-tables

Question 23

Profile data in Power BI

Answer 23

This concept is important because it allows you to shape and organize the data so that interacting with the data and identifying the distribution of the data is uncomplicated. Determines anomalies, examining and developing the underlying data structures, and querying data statistics such as row counts, value distributions, minimum and maximum values, averages, and so on.

Question 24

Column quality

Answer 24

shows you the percentages of data that is valid, in error, and empty. In an ideal situation, you want 100 percent of the data to be valid.

Question 25

Column distribution

Answer 25

shows you the distribution of the data within the column and the counts of distinct and unique values, both of which can tell you details about the data counts. Distinct values are all the different values in a column, including duplicates and null values, while unique values do not include duplicates or nulls.

Question 26

Column profile

Answer 26

gives you a more in-depth look into the statistics within the columns. By default, Power Query examines the first 1000 rows of your data set. To change this, select the profiling status in the status bar and select Column profiling based on entire data set. ]

Question 27

What is Minimum and Maximum value in Column Profile

Answer 27

the minimum value is the first value and the maximum value is the last value when in alphabetical order.

Question 28

Value distribution

Answer 28

Tells you the counts for each distinct value in that specific column. Value distribution feature allows you to pinpoint a place to begin your investigation into why this is so.

Question 29

Column Statistics Include?

Answer 29

Count, Error, Empty, Distinct, Unique, Empty String, Min, Max

Question 30

What is M code

Answer 30

M Query is a “mashup” query language that can be used to query a lot of data from many sources. It is used in the first part of the data import process for Power BI Desktop, which is when data is loaded into the data model and queries are run in the background using M.

Question 31

Difference between M and Dax Language

Answer 31

DAX is used to create calculations and aggregations within the data model, whereas M is used to query and transform data from various sources before loading them into the data model.

Question 32

What data cleaning we can perform in Power Query

Answer 32

We can Remove Columns, rows, Replace errors and Value, Promote first row as header, Change Data type, Split or Merge column, Trim, Group by, Transpose, Pivot, Unpivot, Merge, Append, Extract Value, Fill up or Down, Reverse Rows and Count rows