This article on Power Query contains one line m-code for calculating NETWORKDAYS.INTL using List.Accumulate. Hence, you can control weekends as well as list of holidays.
Author: Vijay A. Verma
Power Query Challenge 3 – Replacement of Words through Lookup Table
There is a table which has Original Text and there is another table which contains the list of words which need to be replaced. Original text should be replaced with words from lookup table.
Power Query Challenge 2 – Count the Frequency of Words
Here comes the challenge. The Subjects table has list of subjects. The words are separated by either comma or space and amount of space is also not uniform. The case of subjects is also not uniform. The count of rows for subjects is needed
Power Query Challenge 1 – Count Number of Words through UI
Here comes the Power Query challenge. The count of words in each row is needed. The challenge is that the solution has to be completely UI (user interface) driven. After loading the table in PQ, you should not press even a single key to come up with the result. You should use only mouse.
Excel Quiz 53
Tips & Tricks 182 – Determine Quarter for Fiscal Year
Few countries follow different quarter other than Q1 from Jan-Mar and Q2 for Apr-Jun. In case of Jan-Mar as Q1, formula is simple (if cell A2 is date) =ROUNDUP(MONTH(A2)/3,0) This will give result as 1, 2, 3 & 4 for the quarters. If you want, you can concatenate "Q" in the formula to show Q1,…
Solution – Challenge 72 – Unique characters string from a given string
A possible solution for the challenge – Challenge 72 – Unique characters string from a given string =CONCAT(UNIQUE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))
Tips & Tricks 181 – Insert Sequence / Serial Numbers in a Pivot Table
Sometimes, it is important to show sequence numbers such as 1, 2, 3…..and so on. If you use database technologies, this is known as index column. 1, 2, 3…..are also used to denote rank. Suppose you have a pivot table which is sorted in descending order for the value. For the sake of showing ranking, you…
Split a Column Dynamically – Power Query – Tips & Tricks 6
Power Query Split a Column Dynamically is a very important tool for PQ practitioners. You can use this to split your column into variable number of columns. This removes limitation of splitting a column on the basis of first time split if result columns are more than first time split columns.
Tips & Tricks 180 – Determine the date given the weeknumber
Given a year and week number, below formula finds the Sunday as the date. =MAX(DATE(F2,1,1),DATE(F2,1,1)-WEEKDAY(DATE(F2,1,1),1)+1+(G2-1)*7) Where F2 has the year and G2 has the week number. For week 1, this would always give 1-Jan as the date.