Data Analyst Based on Excel, Power Query and Power BI
Based on Microsoft's Data/BI products, primarily Excel and Power BI Desktop. Azure & Fabric products like Azure Data Factory, Azure Data Lake, Azure Synapse Analytics, Azure Databricks, Azure Analysis Service, Azure Data Share, Azure Machine Learning and etc. will be recorded in another memo :-) PBI Service related knowledge will be recorded in that memo as well.
301 flashcards · Computer Science · by claudehsu
Recall the auto-fill functionality. For example, we have a bunch of email address in a column. If we manually extract the domain of the email for the first record to its adjacent cell on its right. How to conveniently do the similar operation to all the records below the current one?
Use Ctrl-E to do the auto-fill. It also works with WPS.
What operation can transfer rows to columns, and columns to rows?
The Transpose operation. We can use `transpose(Range)` function. We can also find it in the 'special paste'.
What's the shortcut of asking for the sum of the adjacent sequence of numbers?
ALT+=
Recall the advanced filter operation.
original data section: can be a table filter condition section: including header line filtered data target location: first line of data section or the header line Note Only simple conditions can be handled Complex conditions like age > 10 && age < 20 cannot be 'directly' or 'simply' handled by the filter
Recall the functions that you need to do sum or count when certain conditions need to be applied.
Functions sumifs countifs Below is an example
Below is a formula using countifs, what's the issue of it? =COUNTIFS(D2:D101, ">20",D2:D101," < 50", E2:E101, "=Female")
It's very tricky, but you cannot place a space at the beginning of the criteria. As a result " < 50" needs to be corrected as "< 50"
How to do the 'cascading' validation in Excel? For example
The trick here is to define name for the area and then define the validation with INDIRECT formula to refer to the named area
Given below data table (showing only part of the table) In Excel Pivot table, how to add the calc measure? Hint Use DAX
The trick is to enable 'add this data to the data model' when creating the pivot table. Then use DAX expression to create the calc measure The DAX is like below
In Excel Pivot table, how to show the top N elements under one parent? For example, in below pivot table, we'd like to keep only the first 2 cities under each state.
Do the picking operation in the table and the trick is to right mouse click on the element on the row, instead of clicking on the table header line. Then make the desired configuration. Pay attention to the Measure on which the picking operation will be based on.
In Excel vlookup function, there are 4 parameters lookup_value table_array col_index_num range_lookup The first parameter lookup_value is intuitive. What are the attention points for the other 3 parameters??
table_array: the leftmost column is the one against which the search/look-up is on. col_index_num: index starts with number 1 instead of 0 range_lookup: False for exactly match
Suppose we use 'exact match' option for vlookup in Excel. We may get the error #N/A, if there is no exact match. How to provide a user friendly error message?
Use IFERROR function. For example =IFERROR(VLOOKUP("Hangzho",B1:C13,2,FALSE), "Not Found")
What does 'v' mean in the function vlookup?
'v' stands for vertical. Correspondingly, we have hlookup function in Excel.
The vlookup function and hlookup funcion have the limitation on the 'reference direction'. vlookup finds the value in the column on the right side of the searching column hlookup finds the value in the row below the searching row. How can we break the limitation?
With the latest Office 365, use xlookup With previous versions of Office Excel index + match: support backward search use lookup: sometimes not the 'exact' match For example =INDEX(A1:C13,MATCH("Fuzhou",B1:B13,0),1)
Using Excel advance filter, we cannot specify complex conditions, such as `age > 20 && age <30`. How to deal with those complex conditions?
We can add a column to populate the condition value, and then do further operation according to the value.
Can Power Query in Excel help handle large data such as 10 million level?
Yes. For example, we can make a Pivot Table from 50 million records. Excel has the limitation of row count to around 1m, but PowerQuery has its own system so we don't have that limitation on PowerQuery
Suppose in Excel we have a table named 'CourseTable' containing the data we need. How to make it as the source in our PowrQuery.
let Source = Excel.CurrentWorkbook(){[Name="CourseTable"]}[Content], Result = Table.TransformColumns(Source, {"CourseDescription", Text.Proper}) in Result Use `Excel.CurrentWorkbook` to get all the tables, named areas, dynamic arrays, then keep the desired record and finally get the data of 'CourseTable' by accessing the Content property.
In Power Query Editor | Advanced Editor, we can write M language code directly. The base structure of M language code is let <body_of_query> in <return_value> Fill in the keywords.
The entire structure actually is for populating the <return_value>. <body_of_query> actually is a comma separated expression, which is composed of sub-expressions and those sub-expressions will be evaluated per dependency ordering approach.
Write a simple M Query to return a singular value.
A simple M query to return a singular value let Dog = "Liyu" in Dog The code returns a string "Liyu". If we prefer the very concise form, we can simply use below M code "Liyu" The core is to populate a singular value either using a `let...in` structure or simply using text literal
Does `let` support nested structure? If it does, provide an example
Yes, `let` supports nested structure. let DogA = "Liyu", DogB = let Dog = "Jenis" in Dog in Text.Combine({DogA, DogB}, ", ") In above example, we use the nested `let` to assign value to a variable of the outer `let`. Something vital here is to understand `let` is an expression. let sub-expr1, sub-expr2, sub-expr3, in return-expr The value of the entire `let...in` expression is defined by the last `return-expr`. Those sub-expressions are for calculating the `return-expr`.
How to denote a list in M? Skills = { "M Lang", "DAX", "Python", "Scala" }
The notation is different from most other programming languages.
Try a 2-dim list. Is it treated as a table in Power Query?
No, it's treated as a nested list, or a list of list. However, it's not treated as a table in Power Query.
How to create a range in M? Is the end inclusive or exclusive?
A range is essentially a list. We create a range with `{start..end}`. let OneToSix = {1..6} in OneToSix The end is inclusive.
How to access the first element of a list? Suppose we have a list Dogs = {"Liyu", "Jsc", "Jenis"} How to access the first element "Liyu"? Using built-in function: List.First(Dogs) Subscription form: Dogs{0}
Can we change the element of a list like below? let Dogs = {"Liyu", "Jenis"}, Dogs{0} = "Vicky" in Dogs In above code snippet, we try to change the first element.
No, we cannot change the element. M is very FP style so values are immutable. We can transform existing values to get new values, but we cannot mutate the values directly.
Does M support key-value pair structure?
Yes, that's Record type. It's like the record in a database, or dict in Python. Below is an example let DogLiyu = [Name="Liyu", Rating=5] in DogLiyu Note when writing record literals, don't enclose the key(identifier) with double quotes, unless there are spaces in between words, for example `#"Key with Spaces"`. Leading pond sign is mandatory.
Putting multiple records in a list, can we get a table?
No, we cannot. By doing that, we only get a list of records.
How to get the value from a record, if we have the corresponding key? For example, we have below record Dog = [Name="Liyu", Rating=5] How to get the name of the dog via "Name" key? Answer: DogName= Dog[Name]
How to create a table in M? Write an example
Below is an example let Dogs = Table.FromRecords({[Name="Liyu", Rating=5], [Name="Jenis", Rating=4.5]}) in Dogs The key here is to use Table.FromRecords function. Alternatively, we have many other Table.FromXXX functions to create the table from the corresponding data structure. Below is another example using `Table.FromList` let Dogs = Table.FromList({"Dogs,5", "Jenis, 4.5", "Jsc,4.5"},null,{"Name", "Rating"}) in Dogs We can also use `#table` function to construct a table let DogColumns = {"Na…
`Excel.CurrentWorkbook()` is widely used in the PQ. What does it return, and how to use it?
It returns below items in the current workbook tables named ranges dynamic arrays That means we can define our data in one of above form, for example a table, and then use `Excel.CurrentWorkbook()` to retrieve the desired data. Invoking the function we will get a table Content: another table containing the data Name: name of the items(tables, named ranges, dynamic arrays)
How to denote comments in M? Answer: put the comment line above the transformation line and use leading // to denote the line is a comment line
Suppose we have a named range called Dogs. Below M code returns us a record, instead of a table showing the data in the 'Dogs' named range. Excel.CurrentWorkbook(){[Name="Dogs"]} Why is it?
The reason is <table>{<condition>} returns a record in the <table> according to the <condition>. In this case, to get the data in the `Dogs` named range, we need to further enter the `Content` property of the record, so we can use below M code Excel.CurrentWorkbook(){[Name="Dogs"]}[Content]
Suppose we have below two tables(or named range). How to combine them into a bigger table with M? Note `Name` column and `DogName` column are columns with different names, but they mean the same thing Think about if we simply combine them with the combine function, what will happen?
If we simply use `Table.Combine` function, we will get three columns, Name, DogName and Rating, with `null` value under Name and DogName. To fulfill the combination operation, we have two approaches Rename either `Name` to `DogName`, or `DogName` to `Name`, so that the property name is unified. Define the table/named range without header, so that column names will be assigned by PQ as Column1 Below the implementation of the 1st solution let DogTbl1 = Table.PromoteHeaders(Excel.CurrentWorkbook()…
Suppose we have below tables containing employee info and salary info How to use M to join two tables?
let EmpTbl = Excel.CurrentWorkbook(){[Name="EmpTable"]}[Content], EmpTblRn = Table.RenameColumns(EmpTbl, { {"Column1", "EmpId"}, {"Column2", "EmpName"}, {"Column3", "EmpDept"} }), SalaryTbl = Excel.CurrentWorkbook(){[Name="SalaryTable"]}[Content], SalaryTblRn = Table.RenameColumns(SalaryTbl, { {"Column1", "EmpId"}, {"Column2", "EmpSalary"} }), JoinedTbl = Table.Join(EmpTblRn,"EmpId",SalaryTblRn,"EmpId", JoinKind.Inner) in JoinedTbl Note Table.RenameColumns needs a list of list showing the mappi…
To implement table join or table combine operation, there are below two approaches In one query, define two variables for the two table sources and then do the operation based on the two variables Define two queries respectively, each for one table. Use those two query as source to do the join/combine operation (you may need further transformation or processing to those two queries before join/combine) Which one is the recommended approach?
The latter one is the recommended approach, because it's more flexible and we'll be able to reuse the queries.
M is a functional programming language. In M code snippet, define a simple function called `SumOfSquare` to take two numbers and return the sum of square values.
let SumOfSquare = (a, b) => a*a + b*b in SumOfSquare Note The function literal syntax is similar to the ones in many other FP languages. If the function `SumOfSquare` was defined inside M snippet without being exposed, it would be only used in the current M snippet.
In M code snippet, can we define a function for other queries to use? If we can, how to implement it?
Yes, we can. The trick is to return the function as return value let SumOfSquare = (a, b) => a*a + b*b in SumOfSquare We can also rename the query with a proper function name, and the name of the query will be the reference to the function. Directly invoking the self-defined SOQ function on UI will create a query automatically. We can also invoke the SOQ function in queries as expected
In M code snippet, we can return a function for other queries to use. How to define the type of the function parameters?
Use the form `<param_name> as <type>`. For example SumOfSquare = (a as number, b as number) => a*a + b*b Once types are defined, the UI part will reflect the type by example data as hint For detailed info about types, we can refer to https://learn.microsoft.com/en-us/powerquery-m/m-spec-types
Is it possible to define a recursive function in M? If that's possible, write an example.
Yes that's possible. let SumOfSquareRange = (start as number, end as number) => if start > end then 0 else @SumOfSquareRange(start+1, end) + start*start in SumOfSquareRange The trick here is `SumOfSquareRange` can be invoked with an `@` sign, in an anonymous-function-like context.
Is it possible to define function parameters as optional in M? If that's possible, write an example.
let WalkDog = (name as text, optional dog as text) => if dog = null or Text.Trim(dog) = "" then Text.Combine({name, " is walking Liyu"}) else Text.Combine({name, " is walking ", dog}) in WalkDog Note optional parameters must be placed after those mandatory parameters `=` instead of `==` is used to compare equality, which is quit different from most other programming languages.
Is it possible to define function parameters with default values in M?
No, it's impossible. As a result, in the function body, we need to deal with the case of value absence of optional parameters. On the caller side, it can either omit the optional parameters or pass in null
Here is a challenging task: Implement map-reduce in M To make it simple and clear, limit the parameter types of map and reduce to `number`
Here is an implementation of MapReduce let MapReduce = (map as function, reduce as function) as function => let rsFn = (start as number, end as number) => if(start = end ) then map(start) else reduce(map(start), @rsFn(start+1, end)) in rsFn in MapReduce Named above query as `MapReduce`. Below is an example to use the MapReduce function let double = (n as number) => 2*n, sum = (a as number, b as number) => a + b, sumOfDouble = MapReduce(double, sum), r = sumOfDouble(6, 9) in r Note: We implement…
Another challenging task: Implement the tail recursive version of Map Reduce in M To make it simple and clear, limit the parameter types of map and reduce to `number`
let MapReduceTR = (map as function, reduce as function) as function => let rsFn = (start as number, end as number) as number => let MapReduceTRItr = (itrStart as number, accum as number) as number => if itrStart > end then accum else @MapReduceTRItr(itrStart+1, reduce(accum, map(itrStart))) in MapReduceTRItr(start+1, map(start)) in rsFn in MapReduceTR Note There are multiple 'layers' or nested `let...in...` structures. Think `in` as `return` statement. The entire code is for a query so click on…
Explain the use of `each` with an example.
`each` is a syntactical sugar for `(_) =>`, which means a function with `_` as the parameter. let Dogs = {"Liyu", "Jenis", "Vicky", "Jsc"}, ShortNameDogs = List.Select(Dogs, each Text.Length(_) < 5) in ShortNameDogs Above is an example. Note, `each` itself already means a function, so we don't need to nest it with another function, such as `(dog as text) => each Text.Length(_) < 5`.
Given below M code snippet let DogsWithRating = #table({"DogName","Rating"}, { {"Liyu", 5}, {"Jenis", 4.5}, {"Jsc", 4.5}, {"Vicky", 4} }), HighRatingDogs = Table.SelectRows(DogsWithRating, each [Rating] > 4) in HighRatingDogs Does above M code work?
Yes, the M code works well. In this case, `_` is actually omitted, so it is equivalent to `each _[Rating] > 4]`, meaning to check whether the `Rating` property of the record is greater than 4. `each` is a syntactical sugar using `_` as the parameter name, so we may see some scenarios that `_` is omitted when using `each`.
Given below pivot table How to sort states by their names following asc alphabetical order, while on the city level order by sum value in desc order?
State and city are different levels. In Pivot Table settings, they are two different Pivot Fields. We can sort them separately. Right click on a State element, for example 'CA' in our case, to set the order on State field. Similarly, we can set the order for City field.
Suppose we have below Course info Now we'd like to populate and supplement the Meeting Room info for each course according to below criteria small room: headcount < 300 medium room: 300 <= headcount < 600 large room: headcount >= 600 We have two different designs Write one query and populate the room info on the fly Write two queries One query as the function to populate room type according to the headcount The other one to load the course data with applying the function(the first query) to pop…
The `two queries` approach is better due to better code reusability and flexibility. Another query can use that function to populate room type Later if we'd like to modify the populating logic, for example small room is assigned to headcount < 400 case, modifying the function query is much easier than modifying the populating snippet in a big query Below is the query(function) to populate the room type let PopulateRoomTypeFn = (headcount as number) => if headcount >= 600 then "Large Room" else…
Given below dog info in an Excel table Import the Dog info and apply below two transformation operations change the names of all the dogs to uppercase form change the score from percentage grading system to S,A,B,C,D levels. You can define each level yourself. For example define 'S' as rating greater then or equal to 95
let fnTransRatingToLevel = (rating as number) as text => if rating > 95 then "S" else if rating >85 then "A" else if rating > 75 then "B" else if rating > 65 then "C" else "D", Dogs = Excel.CurrentWorkbook(){[Name="DogsTable"]}[Content], DogsUpperCaseNameWithLevel = Table.TransformColumns(Dogs, { {"DogName", Text.Upper}, {"Rating", fnTransRatingToLevel} }) in DogsUpperCaseNameWithLevel Note We define a function inside of a query, which is considered some code smelly, because it cannot be reused…
Demonstrate the recursion in M. Write an implementation of factorial calculation in M Calculate the sum value of a list of numbers
Factorial implementation let FnFactorial = (n as number) as number => let n = Number.Round(n), r = if n < 0 then Number.IsNaN else if n <=1 then 1 else n*@FnFactorial(n-1) in r in FnFactorial Calculate a list of numbers let SumOfList = (NumbersToSum as list) as number => if List.IsEmpty(NumbersToSum) then 0 else List.First(NumbersToSum) + @SumOfList(List.LastN(NumbersToSum,List.Count(NumbersToSum)-1)), MyNumbers = {1, 5, 23, 91}, Total = SumOfList(MyNumbers) in Total Note We use functions like…
Suppose we need a function to calculate the cube value of a given number. We can define the function as bleow let Cube = (x) => x*x*x in Cube Is there any simplified form of the function?
Yes. We don't need the `let ... in` structure. However, note if the 'body' of the function is not a single expression, we need `let ... in`.
How to understand the `let` expression? For example, below is a code snippet of `let` let Numbers = {5, 23, 91}, secondElement = Numbers{1} in secondElement How to understand above code snippet?
The `let` expression encapsulates a set of values to be computed, assigned to names, and then used in a subsequent expression that follows the `in` statement. In this case, two expressions will be evaluated in sequence Numbers = {5, 23, 91} secondElement = Numbers{1} Then, according to `in` the value of secondElement is the value of the entire `let` expression
Write a `FullName` function to concatenate first name and last name to form a full name. The function takes two parameters FirstName LastName
(FirstName as text, LastName as text) as text => Text.Combine({FirstName, LastName}, " ") Note The `" "` in the `Text.Combine` function is the separator to combine the texts.
Given below code snippet (FirstName as text, MiddleName, optional LastName as text) as text => Text.Combine({FirstName, MiddleName, LastName}, " ") Why `MiddleName` is marked as optional?
Notice the function signature at the bottom of the image. `MiddleName` is defined as `any` type and that causes `optional` displayed along side of it on UI. Notice there is no hint like `Example: abc` in the text inputbox of `MiddleName`. The important thing to remember is the `optional` on UI doesn't mean you don't need to provide the value for `MiddleName`. Below code snippet causes error = FullName("Claude") Per function definition, `MiddleName` is NOT optional, so we need to provide a value…
Given below list of numbers {5, 23, 91} Write a function to take a list as input parameter and transfer it to a list with elements being tripled. With above list, we should get a list {15, 69, 273} as result.
The trick is to use the builtin `List.Transform` function. (Numbers as list) => List.Transform(Numbers, each 3*_) To test above function, we can create another query and invoke the function directly Alternatively, we can define a table Then utilize UI
Given below list of numbers {5, 23, 91} How to get the sum of squares of those numbers using Power Query M? Hint we have two approaches use built-in List.Accumulate function follow a very classic map reduce design
Approach 1: use `List.Accumulate` let MyNumbers = {5, 23, 91}, SumOfSquares = List.Accumulate(MyNumbers, 0, (accum, current) => accum+current*current) in SumOfSquares Note `List.Accumulate` function is the core. We 'combine' map and reduce together in the function literal. Approach 2: follow map-reduce design let MyNumbers = {5, 23, 91}, SquredNumbers = List.Transform(MyNumbers, each _*_) in List.Sum(SquredNumbers) In M, we may find different implementation for the same requirement.
Answer below two variable name related questions Is `2ndElement` a valid variable name in M? If we'd like to name a variable with space, for example `My Numbers`, how can we do that?
Answer: `2ndElement` is NOT a valid variable name because variable names should start with either letters or underscore. In addition, don't put special characters in the variable names If variable names contain space, we can use the form `#"name with space"`
Can we reassign values to a variable in M? For example Name="Power Query", Name="M Lang" Is there any defect?
Yes, there is a defect. `Name` cannot be reassigned. M is a functional programming language. Although `Name` is a variable, instead of thinking `Name` as a variable, think it as a value.
Floating point numbers may not be precisely represented in computer. It's the same case in M. Below is an example How to deal with this precision issue?
It depends. Some system does not require extreme precision and lost on precision is acceptable. In addition, sometimes we may face irrational numbers. However, there will be cases that we do want precise calculation as much as we can. One approach to deal with the issue, is to handle data with small scale. For example, instead of using $5.25 dollars, we can use cent scale, so 523 cents. However, there will still be potential precision issue, for example, dividing $523 cents by 3 people. Another…
Given below M code snippet let Result = List.Transform(MyNums, each _ * 2), MyNums = {1, 3, 5} in Result Is there any defect?
No there isn't. The code works. M uses a dependency ordering approach to evaluate the variables. Here are the details `Result` on line 5 needs to be evaluated when the entire query(the entire M code snippet) is called Line 2 is evaluated to populate `Result`, which in turn detects that `MyNums` on line 3 needs to be evaluated. `MyNums` is evaluated corresponding. That way, M allows us to reference to the variable before its definition. However, that way, in the 'APPLIED STEPS' panel, we can onl…
Write an example to prove the 'closure' concept of FP works in M. Hint: a simple way is to use a nested `let ... in` structure
let Prefix = "[Prefix]]", FnFancyPrefix = (Contents as text) as text => let PrefixedContents = Text.Combine({Prefix, Contents}) in PrefixedContents in FnFancyPrefix `FnFancyPrefix` is returned and the variable `Prefix` in the `Text.Comine` function shows 'closure' concept.
What's the scope of the variables in `let ... in ` structure? Think about in a nested `let ... in` structure, can the outside variables be visited from inner `let...in`?
let Categnory = "Business Intelligence", Result = let Name = "Power Query M Language", InnerResult= Text.Combine({Categnory, Name}," | ") in InnerResult in Result From inner `let...in`, we can visit the variable defined outside, but not the other way around. In addition, variables in the inner shadow the ones with the same name defined in the outside
In M, `Expression.Evaluate` can be used to evaluate string expression. Write an example.
Expression.Evaluate( "Price*Amount", [Price=10, Amount=5] ) Above formula return 50 as the result.
Given below M code snippet. let Expr = "Value.Multiply(Price, Amount)" in Expression.Evaluate(Expr, [Price=10, Amount=5]) Is there any defect?
Yes, there is defect. Checking the signature of `Expression.Evaluate`, you will find the function needs an `environment`(record type) parameter. This parameter provides the namespace for M to look up variables and function. According to the code, in that namespace, we only have two variables, namely `Price` and `Amount`. To fix the issue, we need to include those 'built-in' functions in the namespace. Built-in functions can be represented by `#shared` record, so we correct the code as Expressio…
Given below table Write M code to get the Row Count for each quarter. Ideally we should get something like Q1 - 3 Q2 - 3 Q3 - 3 Q4 - 1
let SalesAmountTable = Excel.CurrentWorkbook(){[Name="SalesAmountTable"]}[Content], SalesAmountCountByQtr = Table.Group(SalesAmountTable, {"Quarter"}, {"Count", Table.RowCount} ) in SalesAmountCountByQtr Note `{"Quarter"}` in `Table.Group` means to group by "Quarter" column. Technically, we can group by multiple columns. The columns used in group by will be in the final result `{"Count", Table.RowCount}` means to create a "Count" column in the final result, and the value of each group is popula…
Given below table We'd like to group the table by quarter, but we don't want to do any aggregation. Instead, we just want to view the 'group by' result. How to do that in M?
let SalesAmountTable = Excel.CurrentWorkbook(){[Name="SalesAmountTable"]}[Content], SalesAmountGroupByQtr = Table.Group(SalesAmountTable, {"Quarter"}, {"GoupDetail", each _} ) in SalesAmountGroupByQtr Note `each _` is used to directly return the group(the small table per the group by)
Given below table We'd like to view below values by each Quarter max sales amount min sales amount average sales amount total sales amount Note try to specify the type of those columns if you can
let SalesAmountTable = Excel.CurrentWorkbook(){[Name="SalesAmountTable"]}[Content], SalesAmountStatisticsByQtr = Table.Group(SalesAmountTable, {"Quarter"}, { {"MaxSalesAmt", each List.Max([SalesAmount])}, {"MinSalesAmt", each List.Min([SalesAmount])}, {"TtlSalesAmt", each List.Sum([SalesAmount])}, {"AvgSalesAmt", each List.Sum([SalesAmount])/Table.RowCount(_), type number} }) in SalesAmountStatisticsByQtr Note Multiple aggregation can be done together We can optionally point out the type like w…
The `Table.Group` accepts a `groupKind` parameter. Explain the use of that parameter. Hint: Use below source data
Basically, we have two options for groupKind `GroupKind.Global`: items in the same group are not listed continuously, but spread in the entire table `GroupKind.Local`: items in the same group are listed continuously. Use this one can improve the grouping performance Below is an example Use `GroupKind.Global` we will have two groups: 'A' and 'B'. Use `GroupKind.Local` we will have three groups: 'A', 'B' and 'A'(yep, another 'A') Give a try with `GroupKind.Local` let ProductTable = Excel.CurrentW…
Given below data table Implement below two queries Query 1: group by Product with case insensitive and calculate Total Amount Query 2: group by length of Product Name(meaning shoes and shirt should be placed in the same group) and calculate Total Amount Query 2 sounds a little abstract, so refer to below result diagram You can map the values of Product into other values to ease the process, but we'd encourage you to use custom comparer function.
Query 1: let Cloths = Excel.CurrentWorkbook(){[Name="ClothsTable"]}[Content], TotalAmountByProductCaseInsensitive = Table.Group(Cloths, {"Product"}, { {"TotalSales", each List.Sum(Amount)} }, GroupKind.Global, Comparer.OrdinalIgnoreCase) in TotalAmountByProductCaseInsensitive Query 2: let Cloths = Excel.CurrentWorkbook(){[Name="ClothsTable"]}[Content], TotalAmountByProductNameLength = Table.Group(Cloths, {"Product"}, { {"ProductNameLength", each Text.Length(List.First([Product]))}, {"TotalSales…
Given below data table By design, Q3 of 2023 and Q2 of 2024 are missing. Write a query to group by Year, meaning all listed Quarters of a year should be placed in one group A 'Quarters' column to show all the involved quarters. Note for 2023 and 2024 there are only 3 quarters Total Amount of the Year You can fill down the Year value, but we'd encourage you to use custom comparer function.
let Inventory = Excel.CurrentWorkbook(){[Name="InventoryTbl"]}[Content], TotalAmountByYear = Table.Group(Inventory, {"Year"}, { {"Quarters", each Text.Combine([Quarter], ", ")}, {"TotalAmount", each List.Sum([Amount])} }, GroupKind.Local, MergeNull2UpperYearComparer), MergeNull2UpperYearComparer = (CurrRec, NextRec) => Number.From(NextRec[Year] is number) in TotalAmountByYear `Number.From` returns 0 if the argument is `false`, and 1 if the argument is `true` The trick here is to understand how…
Given below data table Sort the table as below according to Date in ascending order Amount in descending order Note the sorting icon next to the highlighted columns in above image.
let CoursesTable = Excel.CurrentWorkbook(){[Name="CoursesTable"]}[Content], SortedCoursesTable = Table.Sort(CoursesTable, { {"Date", Order.Ascending}, {"AudienceAmount", Order.Descending} }) in SortedCoursesTable Node To specify asc or desc order, use Order.Ascending or Order.Descending of Order.Type enumeration
Given below data table How to sort the table according to the last character of the 'CourseName' in ascending order? Note The last character of the CourseName may be uppercase or lowercase. Simply sort them according to ascii alphabetical order.
let CoursesTable = Excel.CurrentWorkbook(){[Name="CoursesTable"]}[Content], SortedCoursesTable = Table.Sort(CoursesTable, {each Text.End([CourseName],1)}) in SortedCoursesTable Note by default asc order is used
Given below code snippet, to sort the table based on `CourseName` in ascending order(the default order) let CoursesTable = Excel.CurrentWorkbook(){[Name="CoursesTable"]}[Content], SortedCoursesTable = Table.Sort(CoursesTable, { {each [CourseName]} }) in SortedCoursesTable We will get below error Questions Why does that happen? How to fix it?
The issue happens due to `Table.Sort` is not correctly invoked. To be more specific, the second parameter, comparisonCriteria as any. If we have only one sorting condition on `CourseName`, we can do Table.Sort(CoursesTable, {each [CourseName]}) In above case, compaisonCriteria is a list containing the function only. To specify the sorting order Table.Sort(CoursesTable, {each [CourseName], Order.Descending}) Or we can even make comparisonCriteria a function literal, instead of a list, if default…
Given below data table How to sort the table based on Length of `CourseName` in ascending order Date in ascending AudienceAmount in descending order
let CoursesTable = Excel.CurrentWorkbook(){[Name="CoursesTable"]}[Content], SortedCoursesTable = Table.Sort(CoursesTable, { each Text.Length([CourseName]), "Date", {"AudienceAmount", Order.Descending} }) in SortedCoursesTable The comparisonCriteria is a list in above case, and let's look into its elements 1st element: a function defining to sort according to the length of CourseName, and by default in asc order 2nd element: to sort according to the `Date` column in the default asc order 3rd ele…
Given a list of text like below CourseList = {"C#", "Scala", "F#", "Python", "M Lang", "DAX"} How to sort the list by the length of the text in desc order by the alphabetical order of the text
SortedCourseList = List.Sort(CourseList, { {Text.Length,Order.Descending}, each _ }) Note `each _` is a function. Think it as `(_) => _`
In M, we can define a function like (a as number, b as number) => <fn_body> However, if the function body cannot be expressed by a single expression, what should we do to implement the function?
We need to use the let...in expression. With that structure, we can do (a as number, b as number) => let expr1 = ... expr2 = ... // based on expr1 in expr2 The function literal only allows a single express, so for complex situation, use `let`.
Given below data table How to filter the table and only keep the records whose audience amount is greater than or equal to 300?
let Courses = Excel.CurrentWorkbook(){[Name="CoursesTable"]}[Content], CourseAudienceGE300 = Table.SelectRows(Courses, each [AudienceAmount] >= 300) in CourseAudienceGE300 Note The core function is not named as `Table.Filter`, but `Table.SelectRows`.
Write an example of Key match with a Table, and then analyze whether it's possible to fetch two matched records using this technique?
Below is an example let Dogs = #table(type table[Name=text, Rating=number], { {"Jenis", 4.5}, {"Jsc", 3.5}, {"Vicky", 3.5}, {"Liyu", 5}, {"Bella", 4.5} }), DogLiyu = Dogs{[Name="Liyu"]} in DogLiyu Note The core syntax here is `{[<KeyColumn>=<Value>]}` to extract the desired record As the name of this technique, it's 'key' match. It's a common sense that we don't have duplicate keys in a data structure, so if multiple records are extracted by key matching, we will get an error. Below is the erro…
In regard to configuring data source to get data, we can definitely use UI and then refine the generated M code if needed However, here let's try manually writing some M code to get data. Suppose below is the content of a csv file Course,Headcount,Date Python,200,20240618 DAX,150,20240618 Python,300,20240619 M Lang,250,20240619 DAX,150,20240619 F#,100,20240619 F#,120,20240620 DAX,180,20240620 Write M code to retrieve the data from that csv file. Pay attention to handling the column name using p…
let Course = Csv.Document(File.Contents("C:/Sandbox/MSExcel/courses.csv")), PromoteCourseHeader = Table.PromoteHeaders(Course), TypeCourseColumns = Table.TransformColumnTypes(PromoteCourseHeader,{ {"Headcount", type number}, {"Date", type date} }) in TypeCourseColumns Note It's recommended to get data using UI as a starting point and then refine the generated M code or add more steps as we want.
Given a list as URLs = {"www.python.org", "www.microsoft.com","www.bing.com/nothispageso404", "www.bing.com"} Write M code to check the existence of those URLs and form a result table similar to below one. Note This is a challenging task, and the basic idea is to form a table with one column containing the URLs as the starting point, and then transfer the one-column table into a two-column table to reflect whether the URLs exist or not. Don't try some weird URLs as `thereisnothissite.com` since…
let URLs = {"www.python.org", "www.microsoft.com","www.bing.com/nothispageso404", "www.bing.com"}, FormURLTable = Table.FromList(URLs, null, {"URL"}), AddExistenceColumn = Table.AddColumn(FormURLTable, "Existence?", each CheckURLExistence([URL])), CheckURLExistence = (URL) => let Res = Web.Contents(URL, [ManualStatusHandling = {404}]), StatusCode = Value.Metadata(Res)[Response.Status] in if StatusCode = 404 then "Not Exist" else "Exists" in AddExistenceColumn Note We need to configure to manual…
Lists can be denoted with `{}`, and ranges can be denoted with `..`. Can we create a list with using range inside it? For example MyNumbers = {1, 2, 5..8, 10 } Is above code snippet valid?
Yes, it is fine to use range inside a list.
How to combine list? For example given below three lists ListA = {"DAX", "M Lang"} ListB = {"Python", "Scala"} ListC= {"F#"} How to combine them into one list like below? Hint There are two possible solutions. Try to implement with both :)
We have two approaches `List.Combine` function: List.Combine({ListA, ListB, ListC}) `&` operator: ListA & ListB & ListC Note `List.Combine` needs a list as parameter which contains the lists to be combined.
Create a list with positive numbers less than 30, which can be exactly divisible by number 3. Hint Use `List.Generate` function
List.Generate( () => 3, (Current) => Current < 30, (Current) => Current + 3) Note It's very like the logic of a traditional `for` loop which contains init value, condition check, next step operations. For simplicity, we can use `each` for condition check the next step operations.
Create a list with squares values of numbers from 1 to 9 Hint Use function `List.Generate`
List.Generate( () => 1, each _ < 10, each _ + 1, each _*_ ) Note The trick here is that `List.Generate` function has a `selector` parameter to populate the values to put in the returned list
Given below code snippet MyList = {1..10} ResultList = List.Alternate(MyList, 1, 5, 2) What's the contents of `ResultList`? {1,2,4,5,6,7,8,10} What if we do? ResultList = List.Alternate(MyList, 3, 2) The result is {4,5,9,10}
To construct a table with M, we can do #table({"Name", "Rating"},{ {"Liyu", 5}, {"Jenis", 4.5}, {"Jsc", 4} }) We will get the table as below. However notice the type of the columns are not defined, since ABC123 is displayed on the top-left corner. We can create the table and then transform the type of the column in the following step, but is there a way to create the table with desired type in one step?
Yes, there is. In `#table` , we can pass in a table type to specify the name and the type of the columns. #table(type table[Name=text, Rating=number],{ {"Liyu", 5}, {"Jenis", 4.5}, {"Jsc", 4} }) We will get below result
Given below M code Dogs = #table(type table[Name=text, Rating=number], { {"Liyu",5}, {"Jenis", 4.5}, {"Jsc", 4}, {"Vicky", "Three.Five"} }) What is the result of above M code?
There will not be error even if the value type of the last record is actually NOT number. However, error may happen in the following process. For example if we'd like to sort the table according to Rating SortedByRating = Table.Sort(Dogs, (x,y) => Comparer.Ordinal(x[Rating], y[Rating])) We will get the error If the value is not used, then we'll not get error. For example, if we sort by Name SortedByRating = Table.Sort(Dogs, (x,y) => Comparer.Ordinal(x[Name], y[Name])) It work well.
Given a list like below {"Liyu|5","Jenis,4.5","Vicky|3.5"} How to create a table based on above list? Note the separator of the second element in the list is a comma, not a pipeline Hint Use `Table.FromList` function
Table.FromList({"Liyu|5","Jenis,4.5","Vicky|3.5"}, Splitter.SplitTextByAnyDelimiter({"|",","}), {"Name", "Rating"} ) The Core here is how to utilize the `splitter` parameter of `Table.FromList` function. There are many predefined splitters HOF in M and we can leverage them when needed. By invoking those splitter HOF, such as `Splitter.SplitTextByAnyDelimiter` in above case, we get a function for text splitting. Technically we can define our own text splitting function. It takes a text to split…
Write an example of using `Table.FromColumns` to create a table
Table.FromColumns({ {"Liyu", "Jenis", "Vicky"}, {5, 4.5, 3.5} }, {"Name", "Rating"}) If we'd like to specify the type of the columns Table.FromColumns({ {"Liyu", "Jenis", "Vicky"}, {5, 4.5, 3.5} }, type table[Name=text, Rating=number]) We'll get below result.
We can treat a table being composed of a list of records, or a giant record whose values are all lists. With above concept in mind, we can understand splitting a table horizontal split vertical split Describe above two sorts of split and especially focus on the split result.
Horizontal split: a table can be treated as a list whose elements are several records, we can use `MyTable{0}` to get get the first record in the list, `MyTable{1}` to get the second record, so on an so forth. Vertical split: a table can be treated as a record `[<column1_name>:<column1_values>, <column2_name>:<column2_values>, ...]`. That means `MyTable[Column1]` gives us a list containing all the values under Column1.
Tables can be treated as a list of records. Given below data table Dogs = #table(type table[Name=text, Rating=number], { {"Liyu", 5}, {"Jenis", 4.5}, {"Jsc", 4}, {"Vicky", 3.5} }), Given below M code snippet TargetDog = List.LastN(Dogs, 2) Can we retrieve the last two records of the data table?
No, we'll get below error. `List.LastN` function cannot be applied to tables. For tables, there are corresponding `Table.LastN` funciton TargetDog = Table.LastN(Dogs, 2) Note the result is still a table, not a list of records.
Given below data table Courses = #table(type table[Id=number, Name=text, Date=date], { {1, "DAX", 20240619}, {2, "M Lang", 20240619}, {3, "DAX", 20240620}, {4, "Python", 20240620} }) With below M code snippet PythonCourse = Courses{[Name="Python"]} DAXCourse = Courses{[Name="DAX"]} Can we get desired records of Python and Dax courses? If we cannot, how to fix the issue and get the desired records?
For Python case, we can. However we'll get below error for the DAX case The form `<Table>{[<Key>=<Val>]}` requires key to be used, which means the form is supposed to return only one record according to the Key. For the multiple matching records case, we can use `Table.SelectRows` funciton Table.SelectRows(Courses, each [Name] = "DAX") We can even implement some complex matching logic with `Table.SelectRows` funciton Table.SelectRows(Courses, each List.Contains({"DAX", "M Lang"}, [Name])) With…
Given below data table How to supplement the missing values under `Quarter` column? The desired result is shown as below
let SalesNonStd = Excel.CurrentWorkbook(){[Name="SalesNonStd"]}[Content], SalesNonStdFillDown = Table.FillDown(SalesNonStd,{"Quarter"}) in SalesNonStdFillDown Note Although there is only one column that needs to be filled down, the `Table.FillDown` function expects a list.
Given below data table By simply importing it Excel.CurrentWorkbook(){[Name="CoursePlanning"]}[Content] we get a table with column types not specified. How to transfrom the column types to the proper ones?
We can utilize the `Table.TransformColumnTypes` function let CoursePlanning = Excel.CurrentWorkbook(){[Name="CoursePlanning"]}[Content], TransformColumnType = Table.TransformColumnTypes(CoursePlanning, { {"Id", type number}, {"CoursName", type text}, {"Date", type date} }) in TransformColumnType Below is the result Note In one transform operation, we can transform multiple columns defined in a list
Given below two data tables How to combine two tables and only keep `CourseName` and `Date` part? Ids are local assigned, not global assigned, so not meaningful in the final result. Language only exists in CourseService, so exclude it from the final result
let CourseBI = Excel.CurrentWorkbook(){[Name="CourseBI"]}[Content], CourseService = Excel.CurrentWorkbook(){[Name="CourseService"]}[Content], CombineBIServiceCourse = Table.Combine({CourseBI, CourseService}, {"CourseName", "Date"}) in CombineBIServiceCourse Note The `Table.Combine` function can accept a list to define which columns should be kept in the final result. If we do like to indicate the type of the column, we can do `type table[CourseName=text, Date=date]`, instead of just providing a…
Given below data table If we'd like to know the ranking of the course sales, we can order the table by `Sales` column. However, if we'd like to keep the order of the table, how can we provide the ranking info as below? Note `Table.AddRankColumn` function sorts the table in place, so simply use that function cannot fulfill the requirement, and you may need to figure out another approach.
To satisfy the requirement, we can use `Table.AddColumn` function to add the 'ranking' column. The core here is how we can populate the ranking value for each course. Basically, we have three approaches Treat the `Sales` column as a list. Get a sorted copy of that list and then get the ranking value according to the index/position of the Sales amount For each row, count the number of rows whose amount is greater than the one of the current row. Take 'Scala' row as an example, there is only 1 ro…
Given below two data tables How to show the sales situation with the price for all the products, regardless of whether there is sales amount or not on a product?
Per the requirement, all the product should be included in the final result, so inner join cannot be used. Instead, we should use outer join. let Food = Excel.CurrentWorkbook(){[Name="FoodTable"]}[Content], FoodSales = Excel.CurrentWorkbook(){[Name="FoodSalesTable"]}[Content], FoodPriceNSales = Table.Join(Food, "Name", Table.PrefixColumns(FoodSales, "Sales"), "Sales.Name", JoinKind.LeftOuter) in FoodPriceNSales Note The columns of FoodSales table are renamed with `Table.PrefixColumns` function.…
We can use Power Query UI to do table joins. What's the difference between UI generated M code and manually written M code?
With Power Query UI, the generated code follows below steps to do join Make a nested join using `Table.NestedJoin` Expand the joined table on the 'nested table' column using `Table.ExpandTableColumn`. Note duplicate column names are not allowed, so we may need to provide different names in this expansion step. In manually written code, we usually don't do nested join.
Given below two data tables. How to create a cross joined table, containing the combination of every factory to every product?
We can utilize the idea of nested join and then do expansion. let Factory = Excel.CurrentWorkbook(){[Name="FactoryTable"]}[Content], Product = Excel.CurrentWorkbook(){[Name="FProductTable"]}[Content], CrossNestedJoin = Table.AddColumn(Factory, "ProductTable", each Product) in Table.ExpandTableColumn(CrossNestedJoin, "ProductTable", {"ProductName", "Price"}) Note we're not directly doing nested join, but add a column containing the FProductTable as its cells' values.
JoinKind.Type provides several valid enumeration values. JoinKind.LeftOuter: ensures that all rows of the first table appear in the result. JoinKind.FullOuter: ensures that all rows of both tables appear in the result. Rows that did not have a match in the other table are joined with a default row containing null values for all of its columns. JoinKind.LeftAnti: returns all rows from the first table that do not have a match in the second table.
Semi join such as `JoinKind.LeftSemi` seems very similar to inner join. What's the difference? Write an example to illustrate the difference.
Semi join is similar to inner join because both of them require the records in the left table to have a matching one in the right table. The difference is semi join creates the joined table, using `null` to represent the data in the right table. So semi join works more like to find the records in the left table, which also exist in the right table, but don't retrieve data from the right table. Given below data table let Food = Excel.CurrentWorkbook(){[Name="FoodTable"]}[Content], FoodSales = Ex…
How to concatenate strings in M?
Two approaches `Text.Combine` function Text.Combine({"Liyu", "Jenis", "Vicky"}, "|") `&` operator Number.ToText(523) & "B"