V E Meganathan

Source: The input table includes columns for Date and Sales. In separate range, dropdown is available to select the Start and End Dates. Task: Create a dynamic range that adjusts according to the selected Start and End Dates. Solution: Excel: Method-1: =LET(d,B3:B27,XLOOKUP(E5,d,d):XLOOKUP(F5,d,C3:C27)) Method-2: =LET(d,B3:B35,INDEX(d,XMATCH(F5,d)):INDEX(C3:C35,XMATCH(E5,d))) Method-3: =LET(d,B3:B27,x,XMATCH(E5,d)-1,OFFSET(B3,x,,XMATCH(F5,d)-x,2)) Method-4: =LET(x,XMATCH(E5:F5,B3:B35),TAKE(DR...

Today's discussion focuses on a different technique for unpivoting double header data presented in a cross-tabulated format. While there are many ways to achieve this, most involve transposing the table to merge the double headers into one. However, in this post, I will demonstrate an alternative method that avoids using transpose. let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Cols = {"Product","Year"}, Skip = Table.Skip(Source, each [Column1] is null), Unpivot =...

In this post, I will explain the default behavior of the Table.FromList function in Power Query. Assume our source data has several values within a single column, separated by commas. The goal is to split these values into separate columns and generate a table. Within the Table.FromList function, the list is, by default, treated as a list of text values separated by commas. This default behavior allows us to split the values without needing extra functions. Click on the link below for more...