Dax Coding

Question 1

M is sensitive …

Answer 1

Case Sensitive

Question 2

Steps Names are called

Answer 2

Identifiers if it has a space it will use a hash and quotes

Question 3

Can only combine columns of the

Answer 3

Same type. I cannot combine TEXT &NUMBER must be both the same. It is not like in Excel

Question 4

Text.From

Answer 4

Returns the text representation of value. The value can be a number, date, time, datetime, datetimezone, logical, duration or binary value. If the given value is null, Text.From returns null.
Text.From(value as any, optional culture as nullable text) as nullable text

Question 5

date

Answer 5

• Creates a date value from year year, month month, and day day.
• # date(year as number, month as number, day as number) as date

Question 6

The sequence in which columns are selected when deleting others

Answer 6

Determine the sequence of the final view

Question 7

Step Back

Answer 7

Click the fx button and instead of using the last step use the one before

Question 8

Using steps in conditional filter

Answer 8

1. • Create the filter using a numerical value

2. - Edit the formula and replace the value by the name of the step that contains the dynamic value (eg. Max, Avg ..)

Question 9

Using steps in conditional filter

Answer 9

1. • Create the filter using a numerical value

2. - Edit the formula and replace the value by the name of the step that contains the dynamic value (eg. Max, Avg ..)

Question 10

When you tweak the formula bar

Answer 10

The icon cog dissapears from the stepo view for that particular step

Question 11

Drill Down feature

Answer 11

Restricts the value to whatever was in the cell were ist was applied

Question 12

Muliple selection of columns

Answer 12

You can use a transform function for more than one column at a time, eg replace values

Question 13

Selecting the latest date data

Answer 13

In the filter you have the option to select current: month, year etc

Question 14

Conecting to Web

Answer 14

• Can scrape and connect to web page.
• Can connect to a downloadable file from the website/webpage.
• If connected to a file it could be that the website changes the address of the link in which case the Excel Query wont be able to refresh.

Question 15

Append Data Change Type

Answer 15

Best to leave it for last appended query and not to the individual ones

Question 16

Append Data - Sample File

Answer 16

Is the first file Excel uses and on which it will repeat the steps for the other files to append
It will determine the columns in the final table

Question 17

Append folders filtering

Answer 17

You can filter the files before you append them all I have to do is go to the second step and in the view select the column to filter

Question 18

Import from a workbook, multiple dynamic selection of tabs/sheets

Answer 18

Instead of selecting each tab or multiple tabs, right click in the import window the folder with the name of the workbook and chose transform

Question 19

Combining sheets vs combining files

Answer 19

Combining sheets I dont the combine button but an expand button

Question 20

M Function Excel.CurrentWorkbook()

Answer 20

Brings to the query view the name of the sheets and the tables (to expand)

Question 21

M Function Excel.CurrentWorkbook() double count risk

Answer 21

If the result is copied to the same workbook, becaus the function looks for the CurrentWorkbook it is going to incorporate the sheet result in the query and records would be double counted. YOU NEED to introduce a filter in Query Editor for the name of the sheet that contains the final report