BI Developer Test

You are creating a Power BI report where users need to dynamically filter data based on a range of dates. The report has a Sales table with columns Date, ProductID, and Amount. You decide to use a Power Query parameter to allow users to select a start and end date, which will then filter the Sales table accordingly. The parameter is named DateRange and has a type of List. You need to write a Power Query M formula to filter the Sales table based on the selected date range. The DateRange parameter contains two dates: the first item is the start date, and the second item is the end date. How should the M formula be structured to achieve this functionality?

A: Table.SelectRows(Sales, each [Date] >= DateRange{0} and [Date] <= DateRange{1})

B: Sales{[Date] >= DateRange{0}, [Date] <= DateRange{1}}

C: Table.FilterRows(Sales, each [Date] >= List.First(DateRange) and [Date] <= List.Last(DateRange))

D: Table.SelectRows(Sales, each [Date] >= List.First(DateRange) and [Date] <= List.Last(DateRange))

E: Filter.Table(Sales, [Date] >= DateRange{0}, [Date] <= DateRange{1})

Consider the following two tables in Power BI:

We received the following error when we tried to set many-to-one cardinality between devices to users tables: “The cardinality you selected isn’t valid for this relationship”. What should we do before creating the many-to-one relationship?

Review the following Microsoft SQL Server table:

Complete the following Power Query formula used to create a custom column to replace the numbers present in last 11 characters of ISBN with 11 ‘*’. Example ‘978-0-365-51051-4’ is converted to ‘978-0-***-*****-*’

A: Text.Insert
B: Text.Replace
C: Text.Start
D: Text.End

Here’s sample data imported into Tableau:

Assume only three rows of data is present in the source. Once the data is imported, we added to new calculated fields:

Measure 1 with the formula: SUM([Profit])/SUM([Sales])
Measure 2 with the formula: SUM([Profit]/[Sales])

If we dragged Profit, Sales, Measure 1 and Measure 2 in the viewer as shown here:

Pick the correct statements regarding columns Measure 1, Measure 2 and the row Grand total:

A: Values of Measure 1 would be 0.08, 0.075 and 0.1
B: Values of Measure 2 would be 0.08, 0.075 and 0.1
C: Values of Measure 1 would be 0.08, 0.769 and 0.08
D: Values of Measure 2 would be 0.08, 0.769 and 0.08
E: Values of Measure 1 would be 0.08, 0.155 and 0.255
F: Values of Measure 2 would be 0.08, 0.155 and 0.255
G: Grand total of Measure 1 would be 0.08
H: Grand total of Measure 2 would be 0.08
I: Grand total of Measure 1 would be 0.255
J: Grand total of Measure 2 would be 0.255

What steps would you take to change the following view so that Office Supplies is represented by a different symbol?

A: Click on shapes in the marks area. Then select Office Supplies and the icon of choice and click okay.
B: Right-click on product category, select default properties, and select shape. Then select Office Supplies and the icon of choice and click okay.
C: Right click on the square and select "change shape" from the popup menu. Select the icon of choice and click okay.
D: A, B or C
E: None of these would work

We have the following dataset in Tableau:

Which of the following Marks settings will result in following Pie chart?

A: Drag Name to Color
B: Drag Share to Color
C: Drag Name to Label
D: Drag Share to Label
E: Drag Name to Angle
F: Drag Share to Angle
G: Drag Name to Detail

Consider the following R code:

This script defines a function apply_fun that calculates the mean of a vector and adds it to the standard deviation of the vector. The apply function is then used to apply this function to each column of the data frame df. What will be the value of result after the script is run?

Review the following Dataframe 'bazinga':

Which of the following commands would turn it into the Dataframe shown below:

Consider the following pseudo code:

What will be the state of list_of_matrices after running the script?

What will the following Python code output?

The function high_sess should compute the highest number of events per session of each user in the database by reading a comma-separated value input file of session data. The result should be returned from the function as a dictionary. The first column of each line in the input file is expected to contain the user’s name represented as a string. The second column is expected to contain an integer representing the events in a session. Here is an example input file:

Tony,10
Stark,12
Black,25
Your program should ignore a non-conforming line like this one.
Stark,3
Widow,6
Widow,14

The resulting return value for this file should be the following dictionary: { 'Stark':12, 'Black':25, 'Tony':10, 'Widow':14 }

What should replace the CODE TO FILL line to complete the function?

Below are code lines to create a Python function. Ignoring indentation, what lines should be used and in what order for the following function to be complete:

Consider the following Python code:

In the above code, recursive_search is a function that takes a dictionary (data) and a target key (target) as arguments. It searches for the target key within the dictionary, which could potentially have nested dictionaries and lists as values, and returns the value associated with the target key. If the target key is not found, it returns None.

nested_dict is a dictionary that contains multiple levels of nested dictionaries and lists. The recursive_search function is then called with nested_dict as the data and 'target_key' as the target.

What will the output be after executing the above code?

What does the below function ‘fun’ does?

A: Sum of digits of the number passed to fun.
B: Number of digits of the number passed to fun.
C: 0 if the number passed to fun is divisible by 10. 1 otherwise.
D: Sum of all digits number passed to fun except for the last digit.

Consider the following SQL table:

How many rows does the following SQL query return?

Consider the following SQL table:

Which of the following SQL commands will find the ‘nth highest Sales’ if it exists (returns null otherwise)?

Consider the following SQL table:

Which of the following SQL queries would return the year when neither a football or cricket winner was chosen?

Consider the following SQL table:

What will be the first two tuples resulting from the following SQL command?

Consider the following SQL table:

How many tuples does the following query return?

We want to register students for the next semester. All students have a receipt which shows the amount pending for the previous semester. A positive amount (or zero) represents that the student has paid extra fees, and a negative amount represents that they have pending fees to be paid. The students are in a queue for the registration. We want to arrange the students in a way such that the students who have a positive amount on the receipt get registered first as compared to the students who have a negative amount. We are given a queue in the form of an array containing the pending amount.

For example, if the initial queue is [20, 70, -40, 30, -10], then the final queue will be [20, 70, 30, -40, -10]. Note that the sequence of students should not be changed while arranging them unless required to meet the condition.

⚠️⚠️⚠️ Note:
- The first line of the input is the length of the array. The second line contains all the elements of the array.
- The input is already parsed into an array of "strings" and passed to a function. You will need to convert string to integer/number type inside the function.
- You need to "print" the final result (not return it) to pass the test cases.

For the example discussed above, the input will be:
5
20 70 -40 30 -10

Your code needs to print the following to the standard output:
20 70 30 -40 -10

A manager hires a staff member to keep a record of the number of men, women, and children visiting the museum daily. The staff will note W if any women visit, M for men, and C for children. You need to write code that takes the string that represents the visits and prints the count of men, woman and children. The sequencing should be in decreasing order. 

Example:

Input:
WWMMWWCCC

Expected Output: 
4W3C2M

Explanation: 
‘W’ has the highest count, then ‘C’, then ‘M’. 

⚠️⚠️⚠️ Note:
- The input is already parsed and passed to a function.
- You need to "print" the final result (not return it) to pass the test cases.
- If the input is- “MMW”, then the expected output is "2M1W" since there is no ‘C’.
- If any of them have the same count, the output should follow this order - M, W, C.