M Code Let Expressions Quiz
49 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What keyword should begin a let expression in M Code?

  • BEGIN
  • let (correct)
  • Let
  • define

    • Variables defined in a let expression can be used outside of that expression.

    False

    What is the syntax to represent a null value in M Code?

    null

    In a let expression, each variable is followed by a ______ which allows the let statement to deliver intermediate values.

    <p>comma</p> Signup and view all the answers

    Match the following terms with their definitions:

    <p>let expression = A structure to define variables and calculations in M Code null value = Represents the absence of a value in M Code logical value = A value that can either be true or false identifier = A name that represents a specific value or expression</p> Signup and view all the answers

    What is the expected last keyword in a let expression?

    <p>in</p> Signup and view all the answers

    The output of a query in a let expression must be the identifier of the last query step.

    <p>True</p> Signup and view all the answers

    What comment syntax is used for multi-line comments in M Code?

    <p>/* comment */</p> Signup and view all the answers

    Which tool dramatically improved the ability to clean, transform, and load data from different sources?

    <p>Power Query</p> Signup and view all the answers

    Power Query is only available in Excel and cannot be used with Power BI.

    <p>False</p> Signup and view all the answers

    What programming language is used to create Power Query queries?

    <p>M Code</p> Signup and view all the answers

    The M in M Code stands for __________.

    <p>Mashup</p> Signup and view all the answers

    Match the following Microsoft tools with their functionalities:

    <p>Power Query = Data transformation and loading Power BI Desktop = Reporting and data visualization Excel = Data analysis with worksheets Data Model DAX = Advanced analytics and calculations</p> Signup and view all the answers

    What kind of language is M Code described as?

    <p>Case-sensitive, function-based language</p> Signup and view all the answers

    PivotTable was the preferred method for summarizing data before PivotTable was available.

    <p>False</p> Signup and view all the answers

    In what year was Power BI Desktop invented?

    <p>2015</p> Signup and view all the answers

    What should happen if the splitter function returns more columns than the table expects and ExtraValues.Error is set?

    <p>An error will be raised.</p> Signup and view all the answers

    The default encoding type when saving a CSV file is UTF-8.

    <p>True</p> Signup and view all the answers

    What encoding type corresponds to 1252?

    <p>Windows-1252, ANSI Latin 1; Western European (Windows)</p> Signup and view all the answers

    The option ExtraValues.Ignore is useful when you want to omit extra columns and avoid getting an ________.

    <p>error</p> Signup and view all the answers

    How many parameters can be specified in the columns argument record for importing text files?

    <p>One to five</p> Signup and view all the answers

    Match the following encoding types with their identifiers:

    <p>UTF-7 = 65000 UTF-8 = 65001 Windows-1252 = 1252 IBM865 = 865</p> Signup and view all the answers

    The QuoteStyle.None option means that quoted line breaks are kept.

    <p>False</p> Signup and view all the answers

    A delimiter can be specified in a record like: Delimiter=_____ for a tab.

    <p>#(tab)</p> Signup and view all the answers

    What is the main purpose of the Csv.Document function?

    <p>To convert a text file containing a specified delimiter into a table</p> Signup and view all the answers

    The delimiter argument in the Csv.Document function can only accept a single character.

    <p>False</p> Signup and view all the answers

    Name one file format that the source argument of Csv.Document can accept.

    <p>csv or txt</p> Signup and view all the answers

    The _______ argument allows you to specify which columns to import using null to import all columns.

    <p>columns</p> Signup and view all the answers

    Match the following components of the Csv.Document function with their definitions:

    <p>source = The text file to be converted columns = Specifies which columns to import delimiter = Defines the character used to separate values extraValues = Determines what happens to skipped columns</p> Signup and view all the answers

    Which of the following characters is NOT accepted as a special delimiter in Csv.Document?

    <p>Period</p> Signup and view all the answers

    The extraValues argument has no effect on how columns are skipped in the Csv.Document function.

    <p>False</p> Signup and view all the answers

    What happens if you omit the columns argument in the Csv.Document function?

    <p>All columns are imported</p> Signup and view all the answers

    What does subtracting one date from another yield in M Code?

    <p>A duration value</p> Signup and view all the answers

    In M Code, you can merge a date value and a time value using the addition operator.

    <p>False</p> Signup and view all the answers

    What is the intrinsic function used in M Code to hard code a time value?

    <p>#time(hour, minute, seconds)</p> Signup and view all the answers

    A datetime value in M Code is represented as a serial number that combines the number of days since _______ and the decimal amount of a 24 hour day.

    <p>1/1/0001</p> Signup and view all the answers

    Match the following intrinsic functions with their purposes:

    <p>#date(year, month, day) = Creates a date value #time(hour, minute, seconds) = Creates a time value #datetime(year, month, day, hour, minute, seconds) = Creates a datetime value #duration(days, hours, minutes, seconds) = Creates a duration value</p> Signup and view all the answers

    Which statement regarding time values in M Code is correct?

    <p>Time values can be offset using addition and subtraction.</p> Signup and view all the answers

    The join operator is used in M Code to combine a date and a time value.

    <p>True</p> Signup and view all the answers

    Which operator can be used to offset a time value with a duration?

    <p>Addition and subtraction</p> Signup and view all the answers

    What is the primary use of a left outer join?

    <p>It combines data from multiple tables while keeping all records from the left table.</p> Signup and view all the answers

    The custom function in M Code can use variables that contain spaces.

    <p>False</p> Signup and view all the answers

    What operator is used to indicate the mapping of variables in a custom function?

    <p>=&gt;</p> Signup and view all the answers

    In M Code, the _____ function is similar to Excel's LAMBDA function.

    <p>custom function</p> Signup and view all the answers

    Which of the following statements is true about left anti joins?

    <p>They return records from the left table that do not have corresponding records in the right table.</p> Signup and view all the answers

    Match the following M Code elements with their descriptions:

    <p>Variable Names = Defined using identifiers without spaces Go To Operator = Indicates the start of the variable mapping Value Types = Optional definitions for variable types Comments = Used to add non-executable notes in the code</p> Signup and view all the answers

    Functional value types are mandatory when creating a custom function in M Code.

    <p>False</p> Signup and view all the answers

    What must be used to create a comment in M Code?

    <p>// or multiple hard returns</p> Signup and view all the answers

    The left outer join is the most _____ type of join used in data transformations.

    <p>common</p> Signup and view all the answers

    Study Notes

    Data Analysis Made Easy with Microsoft Power Tools

    • This video covers the fundamentals of M code in Power Query.
    • The video is not as in-depth as other resources.
    • A book with more details on M code will be published in October 2024.
    • This book will contain every detail of M code.
    • Power Query is a Microsoft tool for connecting, importing, cleaning, transforming and loading data.
    • M Code is a programming language usable in Power Query, Power BI Desktop, and the Power BI Online Service (Dataflow)

    Table of Contents

    • Key topics are listed, with page numbers indicating where details can be found in the table of contents.

    History

    • Power Query was introduced in Excel in 2013, 20 years after PivotTables were invented.
    • PivotTables are easy to use with correctly structured tables and column names but Power Query allows for data from different sources.
    • Power Query improves capabilities to take data from multiple data sources and transforms it into a suitable structure before loading it into Excel.
    • Power BI Desktop was invented to make data reporting and visualization easier in 2015.
    • Microsoft Power Query is now an embedded tool in Power BI online service and called Dataflow.

    Power Query and M Code

    • All Power Query queries are written using the M Code programming language.
    • M code is described as "Mashup Data".
    • M Code is a function-based language for creating values such as tables, lists, numbers, and other data-related values.
    • M Code complements and works alongside other Microsoft function-based languages like worksheet formulas, Standard PivotTables and Data Model DAX formulas.

    M Code as Compared to Other Three Functional Languages

    • Worksheet formulas can link to any cell, range, column, or table, unlike M Code, Standard PivotTables and DAX formulas.
    • Standard PivotTables are fast and efficient for summing, counting, and calculating percentages.
    • DAX Formulas are powerful for working with big data because they can create tables at various grains inside DAX formulas.
    • Worksheet formulas instantly update when the data changes, but other languages don't have this feature.

    User Interface & M Code, Three Places to Edit M Code

    • M Code can be edited in the Applied Steps, Formula Bar and Advanced Editor.

    Expression

    • An expression is any M code that results in a value.
    • Examples include functions like Table.ColumnNames(TableName), lists, numbers, query steps within a let expression and a full let expression itself.

    M Code Values

    • Values, with descriptions, are listed.
      e.g., Null, Logical, Text, Number, Time, Date, Datetime, Datetimezone, Duration, Table, Record, List, Binary, Function and Type.

    Type Values and Data Description

    • Data types in M code. (e.g. Decimal number, Currency, Percentage, Date time, Duration, Text, True/False, Binary, Any, nullable.)
    • Data types are used as safeguards to deliver consistent results in calculations in M Code.

    Operators, Values and Data Types

    • Operators in M Code are shown in a table.
    • Precedence and type compatibility are important considerations.
      • Operators for different data types vary.

    Identifiers and Keywords

    • Keywords and identifiers are used in M code.
    • Examples of keywords are let and in.
    • Identifiers act as names for expressions in M Code

    Let Expression

    • let expression lets you define and use variables (query steps) that deliver a final query value.

    Null Value

    • null represents the absence of a value. It can be used in operators, but the result is always null.

    Logical Values

    • Boolean values (true/false).
    • Operators for logical values (e.g greater than >, less than <).

    Text Values

    • Text values are strings of Unicode characters.
    • The syntax for text values. (e.g. "Quad").

    Number Values

    • M code number values.
    • The literal syntaxes for different number values.

    Date, Time, Datetime, Datetimezone and Duration

    • The different date and time related values in M code, including intrinsic functions for setting these values
    • Examples of date, time functions and data type conversions.

    Table, Records, and Lists

    • Data structures in M Code, including Tables, Records, and Lists.
    • How to work with these data structures to perform data analysis.

    List Value

    • List values are sequences of M Code values.
    • Lists can contain a combination of different data types.

    Record Value

    • Records can hold several pieces of information in an organised way.
    • The structure, like the column names in a table, but for a single row.
    • Records can be extracted from tables.

    Table Value

    • Tables are structured to hold column names and multiple values.
    • They structure data as rows and columns and can be used in M code calculations.

    Binary Value

    • Binary values represent a sequence of bytes
    • Used to represent various file types like Excel files, text, images etc.

    M Code Lookup Formulas

    • There are exact match and approximate match lookup formulas.
    • How these lookups are performed in M Code, and how they are different from worksheet (e.g., INDEX) functions.

    Table.Buffer

    • The Table.Buffer function stores tables in memory.

    Table.Group

    • Functions for grouping data in given tables.
    • The types and arguments of the function.

    Comparer Functions

    • Functions used to determine equality and compare values.

    Custom Function Value

    • Function values (custom functions) in M Code
    • The declaration and use of variables in custom functions.
    • Examples and syntaxes of custom functions.

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Related Documents

    05 DAME Video: M Code in Power Query PDF

    Description

    Test your knowledge on let expressions in M Code. This quiz covers topics such as syntax for null values, variables usage, and multi-line comments. Challenge yourself to match terms with their definitions and answer questions about let expression structure.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser