Microsoft DP-500 Practice Test - Questions Answers, Page 10

Box 1: 3

Power Query doesn't start at the first query and work down, it starts at the bottom (last) query and works backwards, so 3 tables from 1 will cause it to process that first source table 3 times.

Box 2: Using Table.Buffer in the Orders query

Table.Buffer buffers a table in memory, isolating it from external changes during evaluation.

Buffering is shallow. It forces the evaluation of any scalar cell values, but leaves non-scalar values (records, lists, tables, and so on) as-is.

Note that using this function might or might not make your queries run faster. In some cases, it can make your queries run more slowly due to the added cost of reading all the data and storing it in memory, as well as the fact that buffering prevents downstream folding.

Example 1

Load all the rows of a SQL table into memory, so that any downstream operations will no longer be able to query the SQL server.

Usage

let

Source = Sql.Database("SomeSQLServer", "MyDb"),

MyTable = Source{[Item="MyTable"]}[Data],

BufferMyTable = Table.Buffer(dbo_MyTable)

in

BufferMyTable

Output

table

Reference: https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up

https://docs.microsoft.com/en-us/powerquery-m/table-buffer

Box 1: Apache Spark pool

An Apache Spark pool provides open-source big data compute capabilities. After you've created an

Apache Spark pool in your Synapse workspace, data can be loaded, modeled, processed, and distributed for faster analytic insight.

Box 2: Dedicated SQL Pool

Dedicated SQL Pool - Data is stored in relational tables

Box 3: Serverless SQL pool

Serverless SQL pool - Cost is incurred for the data processed per query

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/quickstart-create-apachespark-pool-portal

https://www.royalcyber.com/blog/data-services/dedicated-sql-pool-vs-serverless-sql/

Box 1: Join

Box 2: LeftOuter

Left outer join

One of the join kinds available in the Merge dialog box in Power Query is a left outer join, which keeps all the rows from the left table and brings in any matching rows from the right table.

Reference: https://docs.microsoft.com/en-us/power-query/merge-queries-left-outer

Box 1: CALCULATETABLE

CALCULATETABLE evaluates a table expression in a modified filter context.

Syntax: CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

Incorrect:

* SUMMARIZECOLUMNS

SUMMARIZECOLUMNS returns a summary table over a set of groups.

Syntax: SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…,

[<filterTable>]…[, <name>, <expression>]…)

* CROSSJOIN returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables.

Syntax: CROSSJOIN(<table>, <table>[, <table>]…)

* UNION creates a union (join) table from a pair of tables.

Syntax: UNION(<table_expression1>, <table_expression2> [,<table_expression>]…)

Box 2: SAMEPERIODLASTYEAR

SAMEPERIODLASTYEAR returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

Syntax: SAMEPERIODLASTYEAR(<dates>)

The dates returned are the same as the dates returned by this equivalent formula: DATEADD(dates, 1, year)

Example:

The following sample formula creates a measure that calculates the previous year sales of Reseller sales.

= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]),

SAMEPERIODLASTYEAR(DateTime[DateKey))

Box 3: TODAY()

TODAY() returns the current date.

The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.

Example:

The following sample formula creates a measure that calculates the 'Running Total' for Internet sales.

= CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESYTD(DateTime[DateKey]))

Reference: https://docs.microsoft.com/en-us/dax/calculatetable-function-dax

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

https://docs.microsoft.com/en-us/dax/datesytd-function-dax

Step 1: Install the on-premises data gateway (standard mode)

The personal mode is only for a single user, not to be used for a shared dataset.

Step 2: From powerbi.com, add a data source to the gateway clusters

After you install the on-premises data gateway, you can add data sources that can be used with the gateway.

Add a data source

After you fill in everything, select Create. You can now use this data source for scheduled refresh or

DirectQuery against a SQL Server that's on-premises. You see Created New data source if it succeeded.

Step 3: From powerbi.com, configure Dataset1 to use a data gateway.

Connect a dataset to a SQL Server database

In Power BI Desktop, you connected directly to your on-premises SQL Server database, but the Power

BI service requires a data gateway to act as a bridge between the cloud and your on-premises network. Follow these steps to add your on-premises SQL Server database as a data source to a gateway and then connect your dataset to this data source.

Sign in to Power BI. In the upper-right corner, select the settings gear icon and then select Settings.

On the Datasets tab, select the dataset AdventureWorksProducts, so you can connect to your onpremises

SQL Server database through a data gateway.

Expand Gateway connection and verify that at least one gateway is listed.

Under Actions, expand the toggle button to view the data sources and select the Add to gateway link.

On the Gateways management page, on the Data Source Settings tab, enter and verify the following information, and select Add.

On the Datasets tab, expand the Gateway connection section again. Select the data gateway you configured, which shows a Status of running on the machine where you installed it, and select Apply.

Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-personalmode

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-sql