V E Meganathan

The source data includes multiple transactions where the date, product, and quantity are combined into a single column, but their order varies for each transaction.  Our objective is to create three separate columns: Date, Product, and Quantity. Method-1: let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  Result = Table.FromRows(List.Transform(List.Split(Source[Data],3), (x) => [L = List.Sort(x), fin = {L{0},L{2},L{1}}][fin]), {"Date","Product","Qty"}) in  Result Method-2: let...

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 =...