Important M Functions for the exam preparation.

 M Language is used in Power Query Editor. M Stands for Mashup. M translates each of your applied steps in Power Query into formulae. 

The complete documentation of M Language is provided here M Language Reference from Microsoft. And here is the reference guide to 700 functions available in M Language.

Below is the list of M Functions which are important from DA-100 Exam perspective.

Power BI DA-100 Certification Questions

 

Question 1

You have to create a new dashboard for consumption by the Business Analysts in Finance Team in your BI project. The Finance Team wants to have the snapshot of the latest data while they interact with the visualizations on the dashboard.
From the Get Data menu option after selecting SQL Server which Data Connectivity mode should you select?

1. Import
2. DirectQuery

Reference Material for above question:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

________________________________________________________

Question 2

How do you connect to Excel files that contain target figures? The solution should involve minimum manual work when new Excel files are created.

1. ​Connect to the files with Folder connector and use the Combine Binaries functionality
2. Connect to a new file each month, perform transformations, and use Append function in Power Query Editor to combine all targets in the same table

Reference Material for above question:
https://docs.microsoft.com/en-us/power-bi/connect-data/service-excel-workbook-files
https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

________________________________________________________

Question 3

Imagine you have created a query named Customers in a Power BI Desktop to append the rows from three external tables with customer data into a single output table named Customers.
What would be the best approach to add a new fabricated ID column to the Customers table to ensure that every row in the Customers table contains a unique ID value?

1. Modify the customer query by adding a counter column
2. Modify the data model by extending the Customers table with a counter column
3. Modify the Customers query by adding an index column
4. Modify the data model by extending the Customers table with index column

Reference Material for above question:

https://databear.com/data-preparation-part-3-merging-and-appending-queries/

________________________________________________________

Question 4

Which of the following strategies are recommended when designing the queries to import data from an OLTP-style database into a Power BI Desktop project?

1. Normalizing the data model to make it better suited for data analysis and reporting.
2. Denormalizing the data model to make it better suited for data analysis and reporting.
3. Refactoring the imported tables into a multidimensional cube.
4. Refactoring the imported tables into a star schema.

Reference Material for above question:

https://techdifferences.com/difference-between-normalization-and-denormalization.html

________________________________________________________

Question 5

Which of the following statements are true with respect to designing a Power BI Desktop project in DirectQuery mode?

1. All queries within the project must be from a single source database.
2. You cannot add calculated columns to the data model.
3. You cannot convert column data types when editing a query.
4. You cannot add measures to the data model.

Reference Material for above question:

NA

________________________________________________________

Question 6

Which three transformations are available when you right-click a column header in a Query Editor window?

1. Remove
2. Duplicate Column
3. Change Type
4. Count Rows

Reference Material for above question:

NA

________________________________________________________

Question 7

When using the “Split Column by Delimiter” transformation, which option may result in more than two new columns?

1. At the left-most delimiter
2. At each occurrence of the delimiter
3. At the right-most delimiter
4. None of the above

Reference Material for above question:

NA

________________________________________________________

Question 8

Select the feature that allows you to navigate through HTML tags to get the data from website?

1. Get JSON DATA
2. Web Scraping
3. Scaffolding

Reference Material for above question:

NA

________________________________________________________

Question 9

Client provides you with SQL Server Analysis Service database connectivity with Multidimensional database structure.

To import data use Get Data -> SQL Server Analysis Service and data will be imported to Power BI.

Is this statement correct ?

1. TRUE
2. FALSE

Reference Material for above question:

NA

________________________________________________________

Question 10

A dataset is available in Power BI service online. Would you be able to import data from Power BI Service online using Get Data --> Power BI dataset ?

1. Yes
2. No

Reference Material for above question:

NA

________________________________________________________

Question 11

What is the use of "Select Related Table" option while importing data from SQL Server?

1. It will import the tables that have similar schema.
2. It won't import any extra table or view.
3. It will import tables that have physical relationship with selected table.
4. It will also import views related to selected tables.

Reference Material for above question:

NA

________________________________________________________

Question 12

What happens to the data types of Query Editor that are not available in data model while loading data?

1. Tables with not supported types will throw an error.
2. Data with these types will not load into data model.
3. These types will be converted to related data types.

Reference Material for above question:

NA

________________________________________________________

Question 13

Which view shows the graphical representation of data tables structure in query editor?

1. Advance Editor
2. Manage Data-Source
3. Query Dependency

Reference Material for above question:

NA

________________________________________________________

Question 14

What is the purpose of TRIM function?

1. It will remove white space from a string.
2. It trims a specific character from string.
3. It will remove white space from start and end of a string.

Reference Material for above question:

NA

________________________________________________________

Question 15

The marketing team needs a new dashboard to show the uptake of social users on the company’s corporate forum. The dashboard will contain one particular report that shows the total number of users that registered for the site in each of the months of the year. For this report you want to create a dataset with a calculated column that displays the date with the abbreviated month name followed by the four digit year e.g. Jan 2018

Which DAX expression should you use?

1. FORMAT([RegistrationDate],"MM yyyy")
2. FORMAT([RegistrationDate],"mm yyyy")
3. FORMAT([RegistrationDate],"MMM yyyy")

Reference Material for above question:

NA

________________________________________________________

Question 16

A large corporate project requires the data team to create a number of different Power BI reports for the company’s internet sales data.

You need to create a measure that estimates the variance of the SalesAmount_USD column from your InternetSales_USD table, for the entire population. The mathematical formula for this calculation should follow the agreed standard: ∑(x - x̃)²/n

Which DAX formula should you use?

1. VAR.S
2. VAR.P

Reference Material for above question:

NA

________________________________________________________

Question 17

Power BI comes with a range of visualizations to portray any data story you wish. There is one particular visual that’s perfect to help users understand and analyze cash-flow during the ups and downs of all months in a financial year. Which visualization is this?

1. Waterfall chart
2. Scatter Chart
3. Treemap
4. Stacked column chart

Reference Material for above question:

NA

________________________________________________________

Question 18

You want to create a line chart showing Quantity by the invoice date. You notice that there is a relationship between the Date from the Date table and the Invoice Date Key from the Sale table, but the relationship is inactive. 

All other visuals in your report will be analyzing values by delivery date. How should you approach this problem?

1. Delete the relationship based on the delivery date and activate the relationship based on the invoice date.
2. Create the following measure.                                                              Quantity by Invoice Date:= CALCULATE(SUM (Sale[Quantity]), USERELATIONSHIP('Date'[Date], Sale[Invoice Date Key]))
3. Activate the relationship based on invoice date, keeping the other relationship as is.
4. Use the TREATAS function.

Reference Material for above question:

NA

________________________________________________________

Question 19

To create a column chart that displays Quantity by Color and can be drilled down to show Quantity by Stock Item. Which of the following solves the problem? More than one answer can be correct.

1. Put color on Axis and Stock Item on Tooltips.
2. Create a Stock Item Hierarchy that includes Color and Stock Item and use the hierarchy on axis.
3. Put Color and Stock Item on axis.
4. Put Color on axis and Stock Item on legend.

Reference Material for above question:

NA

________________________________________________________

Question 20

Using the bookmarking features in the report designer in Power BI Desktop, there is a limitation of creating only one bookmark per page in a report.

Is this statement correct?

1. TRUE
2. False

Reference Material for above question:

NA

________________________________________________________

Question 21

Which of the following statements are true with regard to using the Slicer visual in a Power BI report?

1. A Slicer visual is used on the Power BI reports to provide the report consumer with interactive filtering behavior.
2. A Slicer visual from a Power BI  report can be pinned to a dashboard to make the dashboard interactive.
3. A Slicer visual based on a Date Column can be configured to use slider controls.
4. When adding a field to the Field well of a Slicer Visual, You can use either a column or a measure.

Reference Material for above question:

NA

________________________________________________________

Question 22

Which of the following visuals is not included as part of Power BI and must be added as a custom visual?

1. Waterfall
2. Histogram
3. Donut
4. Stacked Area

Reference Material for above question:

NA

________________________________________________________

Question 23

What is the file format used to define a theme for a Power BI report?

1. Cascading Style Sheets(CSS)
2. JavaScript Object Notation(JSON)
3. Extensible Markup Language(XML)
4. Power BI Markup Language(PBIML)

Reference Material for above question:

NA

________________________________________________________

Question 24

In Power BI Desktop Report view, which three options will create a measure in the Sales table?

1. Create a measure from any table and then set the Home Table properties to Sales.
2. Select the Sales table in the Fields List, and then click New Measure in the Modeling ribbon.
3. On the Fields list, Click the ellipsis next to sales table or right-click in the Sales table and then click New Measure.
4. Click New Measure, and then type in the formula Home Table = Sales

Reference Material for above question:

NA

________________________________________________________

Question 25

What are the three reasons for which you might use calculated tables?

1. To improve the speed and performance of an existing table.
2. To create a table based on the result of a function or formula.
3. To union tables.
4. To perform different types of merge join.

Reference Material for above question:

NA

________________________________________________________

Question 26

Which three fields are available when creating a Pie chart visualization?

1. GROUP
2. Legend
3. Details
4. VALUE

Reference Material for above question:

NA

________________________________________________________

Question 27

By default, which filter level behaves the same as a slicer in a report in Power BI Desktop?

1. Visual Level Filter
2. Report Level Filter
3. Page Level Filter
4. Query Level Filter

Reference Material for above question:

NA

________________________________________________________

Question 28

Which two field options are available when creating a waterfall chart?

1. X-axis
2. Category
3. Y-axis
4. SIZE

Reference Material for above question:

NA

________________________________________________________

Question 29

Months are sorted alphabetically in Stacked bar chart. What can you do to sort it by month number?

1. Click on ellipses on Right Top Corner of visual --> Sort by -- MonthNumber
2. Right Click on field in report view and select sort by, then Select MonthNumber
3. Modeling -> Select Month Column -> Sort By - MonthNumber

Reference Material for above question:

NA

________________________________________________________

Question 30

What is true about using variables in DAX?

1. Variables are created only one time in the current context.
2. Maximum of 5 variables can be created per formula.
3. Variables must be defined inside return.

Reference Material for above question:

NA

________________________________________________________

Question 31

You’re creating a fancy little dashboard using Power BI Service. The dashboard shows the accumulative increase in the global population over the last several decades. The dashboard will contain an introductory YouTube video that you have uploaded to the YouTube website. After uploading the video you copy the embedded HTML code and paste it into your dashboard.

Unfortunately the video is fixed to 560 pixels wide and you would like it to expand to fit the tile size. What should you do?

1. Change the iFrame width and height attribute values to 100%.
2. Remove the allowfullscreen parameter from the code.
3. Add an extra outer DIV with a style width:auto and height:auto value.
4. Videos are fixed size and can't expand to fit tiles.

Reference Material for above question:

NA

________________________________________________________

Question 32

How can you make sure that each category manager can see sales of their category only and CEO to see all sales in the same report?

Your solution must involve minimal effort.

1. Create one report for each category manager, with a different category filter in each report, and a separate report for the CEO.
2. Implement Dynamic row-level security.
3. Create a category slicer in the report.

Reference Material for above question:

NA

________________________________________________________

Question 33

In order to use the Power BI web part in SharePoint Online, the report author as well as all the report consumers require a Power BI Pro license.

Is this statement true ?

1. TRUE
2. FALSE

Reference Material for above question:

NA

________________________________________________________

Question 34

Which of the following types of real-time data set in Power BI always store their data in Azure SQL Database?

1. Streaming Datasets
2. Push Datasets
3. Hybrid Datasets

Reference Material for above question:

NA

________________________________________________________

Question 35

Which of the following are advantages that Power BI dashboards have over Power BI reports?

1. Dashboards support using the Natural Language Q&A features of Power BI.
2. Dashboards provide a better interactive filtering and drill down experience.
3. Dashboards update automatically whenever the data in the underlying data set is updated.
4. Dashboard provide support for data-driven alerts.

 Reference Material for above question:

NA

________________________________________________________

Question 36

Which of the following are advantages that Power BI reports have over Power BI dashboards?

• Reports provide a better interactive filtering and drilldown experience.
• Reports provide story telling capabilities using bookmarks.
• Reports provide a better real-time experience when displaying data from a streaming dataset.
• Report definitions can be saved using an external file format(PBIX) and checked into source control.

Reference Material for above question:

NA

________________________________________________________

Question 37

Which three properties of a tile can be edited in the Power BI dashboard from the Tile details window?

1. Title
2. Subtitle
3. Destination Link
4. Color Scheme

Reference Material for above question:

NA

________________________________________________________

Question 38

What do you need to append to the URL of a Power BI dashboard in order to open it with the navigation pane collapsed by default?

1. ?navigationPane=false
2. ?navigationPane=collapsed
3. ?collapseNavigation=true
4. ?collapseNavigation=collapsed

Reference Material for above question:

NA

________________________________________________________

Question 39

Which feature is used to create alternative name for fields in tables. It will make it much easier for users to ask questions on dashboards?

1. Duplicate Name
2. Alternative
3. Synonyms

Reference Material for above question:

NA

________________________________________________________

Question 40

Which page view types are available in Power BI Report View?

1. Full Screen
2. Fit to Page
3. Fit to Width
4. Actual Size

Reference Material for above question:

NA

________________________________________________________