Prepare the Data (Get Data) Flashcards

Question

What is the most popular way to get data into Power BI?

Answer 1

The most popular way to use data in Power BI is to import it into a Power BI dataset.

Answer 2

The Import mode allows you to create a local Power BI copy of your datasets from your data source. You can use all Power BI service features with this storage mode, including Q&A and Quick Insights. Data refreshes can be scheduled or on-demand. Import mode is the default for creating new Power BI reports.

Answer 3

DirectQuery storage mode allows you to query the data in the data source directly and not import a copy into Power BI. DirectQuery is useful because it ensures you are always viewing the most recent version of the data.

Answer 4

Allows you to identify some data to be directly imported and other data that must be queried. Any table that is brought in to your report is a product of both Import and DirectQuery modes. Using the Dual mode allows Power BI to choose the most efficient form of data retrieval.

Answer 5

No built-in date hierarchy: When importing data, every date/datetime column will also have a built-in date hierarchy available by default. For example, if importing a table of sales orders including a column OrderDate, then upon using OrderDate in a visual, it will be possible to choose the appropriate level (year, month, day) to use. This built-in date hierarchy isn't available when using DirectQuery. If there's a Date table available in the underlying source, as is common in many data warehouses, then the DAX Time Intelligence functions can be used as normal. Date/time support only to second accuracy: When using time columns in your dataset, Power BI only issues queries to the underlying source to a level of detail of seconds. Queries aren't sent to the DirectQuery source for milliseconds. Remove this part of the times from your source columns. Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Additionally, the DAX scalar functions, such as LEFT(), that are allowed, are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used. No support for parent-child DAX functions: When in DirectQuery mode, it's not possible to use the family of DAX PATH() functions that generally handle Parent-Child structures, such as chart of accounts, or employee hierarchies. Calculated tables: You can use calculated tables in DirectQuery when using composite models. Relationship filtering: For information about bi-directional filtering, see Bidirectional cross-filtering. This whitepaper presents examples in the context of SQL Server Analysis Services. The fundamental points apply equally to Power BI. No Clustering: When using DirectQuery, it's not possible to use the Clustering capability, to automatically find groups.

Answer 6

Quick Insights isn't supported: Power BI Quick Insights searches different subsets of your dataset while applying a set of sophisticated algorithms to discover potentially interesting insights. Given the need for very high-performance queries, this capability isn't available on datasets using DirectQuery. Using Explore in Excel will likely result in poorer performance: You can explore your data by using the Explore in Excel capability on a dataset. This approach allows Pivot Tables and Pivot Charts to be created in Excel. While this capability is supported on datasets using DirectQuery, the performance is generally slower than creating visuals in Power BI, and therefore if the use of Excel is important for your scenarios, this fact should be accounted for in your decision to use DirectQuery. Hierarchies are not shown in Excel: When connecting using DirectQuery from Excel to an Azure Analysis Services model or Power BI dataset, for example using Analyze in Excel, any hierarchies defined in the model or dataset are not shown. Maximum length for text columns: The maximum length of the data in a text column for datasets using DirectQuery is 32,764 characters. Reporting on longer texts than that will result in an error.

Answer 7

By default, datasets refresh every hour but can be configured as part of dataset settings to be between weekly and every 15 minutes.

Answer 8

A time-out of four minutes is applied. Queries taking longer than that will fail

Answer 9

Azure Analysis Services is an Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and creates calculations on the data.

Answer 10

The calculations are built using data analysis expressions (DAX). Azure Analysis Services is similar to the data modeling and storage technology in Power BI.

Answer 11

Analysis Services cubes have calculations already in the cube, which will be discussed in more detail later. 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).

Answer 12

You can: Authenticate to the server. Pick the cube you want to use. Select which tables you need.

Answer 13

You use the Get data feature in Power BI Desktop.

Answer 14

Connect Live is a new option in Azure Analysis Services that helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI.

Answer 15

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.

Answer 16

Dataverse lets you securely store and manage data that's used by business applications. Data within Dataverse is stored within a set of tables.

Answer 17

Dataverse includes a base set of standard tables that cover typical scenarios, but you can also create custom tables specific to your organization and populate them with data by using Power Query.

Answer 18

The Performance Analyzer tool helps fix problems and streamline the process. Performance Analyzer can help you identify visuals that are impacting the performance of your reports, and identify the reason for the impact.

Answer 19

In Power BI Desktop select the View ribbon, and then select Performance Analyzer to display the Performance Analyzer pane. Once selected, the Performance Analyzer is displayed in its own pane, to the right of the report canvas. To have Performance Analyzer begin recording, simply select Start recording. Any actions you take in the report are displayed and logged in the Performance Analyzer pane, in the order that the visual is loaded by Power BI. After you've interacted with elements of the report you want to measure with Performance Analyzer, you can select the Stop button.

Answer 20

To clear out the information in the Performance Analyzer pane, select Clear. All information is erased and is not saved when you select Clear.

Answer 21

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 are actively making transformations.

Answer 22

The query folding within Power Query Editor helps you increase the performance of your Power BI reports

Answer 23

The reason for implementing this process is to ensure that these transformations can take place in the original data source server and do not overwhelm Power BI computing resources.

Answer 24

More efficiency in data refreshes and incremental refreshes. When you import data tables by using query folding, Power BI is better able to allocate resources and refresh the data faster because Power BI does not have to run through each transformation locally. Automatic compatibility with DirectQuery and Dual storage modes. All DirectQuery and Dual storage mode data sources must have the back-end server processing abilities to create a direct connection, which means that query folding is an automatic capability that you can use. If all transformations can be reduced to a single Select statement, then query folding can occur.

Answer 25

In Power Query Editor, you go to the Query Settings pane and right-click the last applied and If the View Native Query option is available you can select it to see the native language. If the View Native Query option is not available (not displayed in bold type), then query folding is not possible for this step, and you will 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.

Answer 26

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

Answer 27

Created to show the number of a row in a query

Answer 28

If you can translate a transformation into a Select SQL statement, which includes operators and clauses such as GROUP BY, SORT BY, WHERE, UNION ALL, and JOIN, you can use query folding.

Answer 29

Query diagnostics

Answer 30

It allows you to determine what bottlenecks (if any) exist while loading and transforming your data, refreshing your data in Power Query, running SQL statements in Query Editor, and so on.

Answer 31

To access query diagnostics in Power Query Editor, go to Tools in the Home ribbon. When you are ready to begin transforming your data or making other edits in Power Query Editor, select Start Diagnostics on the Session Diagnostics tab. When you are finished, make sure that you select Stop Diagnostics.

Answer 32

Process as much data as possible in the original data source. Power Query and Power Query Editor allow you to process the data; however, the processing power that is required to complete this task might lower performance in other areas of your reports. Generally, a good practice is to process, as much as possible, in the native data source. Use native SQL queries. When using DirectQuery for SQL databases, such as the case for our scenario, make sure that you are not pulling data from stored procedures or common table expressions (CTEs). Separate date and time, if bound together. If any of your tables have columns that combine date and time, make sure that you separate them into distinct columns before importing them into Power BI. This approach will increase compression abilities.

Answer 33

Relational source systems often have many people who are concurrently using the same data in the same database. Some relational systems and their administrators seek to limit a user from monopolizing all hardware resources by setting a query timeout. These timeouts can be configured for any time span, from as little as five seconds to as much as 30 minutes or more.

Answer 34

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.

Answer 35

This error is self-explanatory. Power BI expects to find data formatted as a table from Excel. The error event tells you the resolution. Perform the following steps to resolve the issue: Open your Excel workbook, and highlight the data that you want to import. Press the Ctrl-T keyboard shortcut. The first row will likely be your column headers. Verify that the column headers reflect how you want to name your columns. Then, try to import data from Excel again. This time, it should work.

Answer 36

usually, this error is caused by the file moving locations or the permissions to the file changing. If the cause is the former, you need to find the file and change the source settings.

Answer 37

This situation happens because of an error in interpreting the data type in Power BI. The resolution to this error is unique to the data source. For instance, if you are importing data from SQL Server and see blank columns, you could try to convert to the correct data type in the query.

Answer 38

Serves as a way to easily store and manage a value that can be reused

Answer 39

Dataflows are stored in a centralized location (Power BI Service) and are useful when you want your power query queries to be used across your organizations. They can be used in multiple files.

Answer 40

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.

Answer 41

``` # of distinct and unique values - distinct # referes to how many different values are in the column ``` -unique # referes to the values that occur only once Distinct and unique values will be the same if all values are unique

Answer 42

Column statistics - Count, # of errors, # of empty, distinct, unique, min, max, average, even, odd

Answer 43

Power Query Editor determines data anomalies by using the Column Distribution feature.

Answer 44

Occasionally, you might find that your data sources contain null values. For example, a freight amount on a sales order might have a null value if it's synonymous with zero. If the value stays null, the averages will not calculate correctly. One solution would be to change the nulls to zero, which will produce the more accurate freight average. In this instance, using the same steps that you followed previously will help you replace the null values with zero.

Answer 45

If you have undesirable values

Answer 46

Match entire cell contents - if this option is checked, Power Query won't' replace values where the "replace with" value is only part of the value to find value 2. IReplace using special characters - this is used to insert special character codes, such as carriage return or nonbreaking space, in the replace with or value to find box

Answer 47

Special characters

Answer 48

A dataset that has already been prepared and publised to Power BI Service

Answer 49

1. Ensures consistent data across multiple reports 2. You aren't copying data needlessly 3. You can create a copy of an existing report and modify it (this takes less effort than starting from scratch)

Answer 50

Select the Use First Row as Headers option on the Home tab Select the drop-down button next to Column1 and then selecting Use First Row as Headers.

Answer 51

Right-click the header, select Rename, edit the name, and then press Enter. Alternatively, you can double-click the column header and overwrite the name with the correct name. You can also work around this issue by removing (skipping) the first two rows and then renaming the columns to the correct name.

Answer 52

The first method is to select the columns that you want to remove and then, on the Home tab, select Remove Columns. Select the columns that you want to keep and then, on the Home tab, select Remove Columns > Remove Other Columns.

Answer 53

When importing data from Excel

Answer 54

*Change rows into columns* If the data that you are shaping is flat (in other words, it has lot of detail but is not organized or grouped in any way), the lack of structure can complicate your ability to identify patterns in the data. You can use the Pivot Column feature to convert your flat data into a table that contains an aggregate value for each unique value in a column. For example, you might want to use this feature to summarize data by using different math functions such as Count, Minimum, Maximum, Median, Average, or Sum.

Answer 55

*Change columns into rows* Unpivoting is a useful feature of Power BI. You can use this feature with data from any data source, but you would most often use it when importing data from Excel.

Answer 56

select Remove Rows > Remove Top Rows on the Home tab.

Answer 57

In Power BI Desktop, you can connect to data created by Power Platform dataflows just like any other data source in Power BI Desktop. Select Power Platform > Dataflows.

Answer 58

In Power Query Editor, in the Queries pane to the left of your data, select the query that you want to rename. Right-click the query and select Rename. Edit the current name or type a new name, and then press Enter. You can also double click the query to rename it.

Answer 59

Select the column that contains the value that you want to replace (Attribute in this case), and then select Replace Values on the Transform tab. In the Value to Find box, enter the name of the value that you want to replace, and then in the Replace With box, enter the correct value name and then select OK. In Power Query, you can't select one cell and change one value, like you might have done in Excel.

Answer 60

Null Values

Answer 61

One solution would be to change the nulls to zero

Answer 62

You can achieve this action by selecting a column, right-clicking on the header of the column, and then selecting the Remove Duplicates option.

Answer 63

copying the table before removing the duplicates. Copying the table before removing duplicates will give you a comparison of the tables and will let you use both tables, if needed.

Answer 64

Certain calculations, deriving hierarchies, or creating proper relationships with other tables.

Answer 65

in Power Query Editor in the Power BI Desktop Report view by using the column tools. *It is best to change the data type in the Power Query Editor before you load the data.* Another method is to select the data type icon next to the column header and then select the correct data type from the list.

Answer 66

You can combine tables into a single table in the following circumstances: - Too many tables exist, making it difficult to navigate an overly- complicated data model. - Several tables have a similar role. - A table has only a column or two that can fit into a different table. -You want to use several columns from different tables in a custom column

Answer 67

Merge & append

Answer 68

Adds rows when you have the same columns | *column headers are the same)

Answer 69

Adds columns when you have the same rows

Answer 70

You can also choose how to join the two tables together, a process that is also similar to JOIN statements in SQL. These join options include: -Left Outer - Displays all rows from the first table (left) and only the matching rows from the second. -Full Outer - Displays all rows from both tables. -Inner - Displays the matched rows between the two tables.

Answer 71

The Value distribution graph tells you the counts for each distinct value in that specific column

Answer 72

Use Advanced Editor to modify M code

Answer 73

- you can do more by writing M queries yourself - M is case sensitive language - you can view & edit full M queries in the advanced editor - the query starts with "let" & ends with "in"

Answer 74

When working with large datasets, it may be a good idea to implement incremental refresh which keeps the bulk of your data static and only refreshes the most recent data that has changed/.