V E Meganathan

Remove repeated consecutive values in Qtr and Month Column. The opposite operation to the Fill Down approach. Workbook Link: https://1drv.ms/x/c/a0ca510549929b21/IQBEM8uCS0QqS7nuLSC0vbOuAfKujE7raDGz38_Ocmy3gJw?e=4NH0wV Solution: Excel: =HSTACK(MAP(B2:C14,LAMBDA(x,IF(COUNTIF(TAKE(B2:x,,-1),x)=1,x,""))),D2:D14) Power Query: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Result = Table.FromColumns(List.Transform(Table.ToColumns(Source[[Qtr],[Month]]), (f) => List.Accumulate(...

Tricks for Selecting a Range Using the GOTO Feature: Imagine your active cell is D3 and you want to select the range from A1 to D3. Method-1: Press CTRL + G to open the GOTO dialog box, enter A1 in the reference box, then instead of pressing ENTER, hold the SHIFT key and press ENTER. Method-2: You can enter formulas like INDEX, CHOOSE, or INDIRECT in the reference box, which return a reference. Then, hold the SHIFT key and press ENTER. =CHOOSE(1,A1,B1,D1) The formula above returns A1 as a...

Task: Generate the result table as shown. Solution: Excel: =MAP(A2:D4,LAMBDA(x,CONCAT(TAKE(x:D4,1)))) =LET(a,A2:D4,b,COLUMNS(a),LEFT(BYROW(a,CONCAT),SEQUENCE(,b,b,-1))) Power Query: let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], Result = Table.FromRows(List.Split(List.TransformMany(Table.ToRows(Source), each List.Reverse(List.Positions(_)), (x,y) => Text.Combine(List.FirstN(x,y + 1))), Table.ColumnCount(Source))) in Result Python in Excel: df = xl("A2:D4") fin =...