You can create a date dimension table, and a product dimension table. I would leave it in the format of three columns, Name, Date and val. However, I recommend you do not do this step. Now, to get to your desired output, you would select Name, Click Pivot Column, Use val as the value column and in advanced settings change to "Don't Aggregate". You can rename the Val - Copy, to "Date" Now, filter the " Name" column to remove blanks With Val - Copy, selected, apply a " Fill Down" Right click Val - Copy, and select "Replace Errors". In the new column Val - Copy, change the data type to "Date" (found in Data Type in the transform tab) Duplicate the column Val (Right click the column, and select duplicate column) Output should look like this (my data is random and differnt to yours). This should keep the product name in one column, and append all the other columns into one. Replace your data with the bit in red ( see more on editing M code). #"Replaced Value" = Table.ReplaceValue(Source,"","Date",Replacer.ReplaceValue,) If you only press Enter (without Ctrl + Shift) Excel won’t recognize your formula as an array formula. The curly brackets will be created automatically.Try this = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVFNC4JAFPwr4VlhP9q37jH7OARB0FE8WAmdKsQO/vt2FJe3sAUyDM9545uxrrPLo+sGmeWZf4Qsju2zkGbmh+4a+KntA9+8ezYfwZucOZ371/1zG1Ybz5WGjjxo4cE5zGRaX3kuIZBrDxavzLRJaf3Wc4K0BCiAVpgxfxUnI5aMWDJiyYglo9iJJyOL+/BRKRCNsEcqvVAtAjsBXjk0aMq0HtGcCCqkLgHuh/9uOYjQH02b8s89exhCb3W4TM3nBb2Oq7OsOsuqs6y6MB/BI6eoOtxnlr8Non0PzRc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta ) in type table ), Select the whole paste area (or make sure it’s still selected after pasting the formats).Īnd press Ctrl + Shift + Enter.There are two reasons for this step: Getting to know the exact range for the transposed cells and at the same time already having the formats of the original cells. Now, instead of values, just the original formatting is transposed. Select “Transpose” and confirm with “OK”.Select the left top cell of the area you want to paste them to and open the paste special window by pressing Ctrl + Alt + v.Copy the cells, which you want to transpose and link.So if the cell range of your source data changes, you usually have to set up the array formula again.įollow these steps for setting a the array formula for pasting cells linked to their sources and transposing them (the numbers are corresponding with the picture on the right hand side): Furthermore it comes with the disadvantages of all the array formulas: Their size is not (easily) changeable. Despite the advantage of not having to do anything manually, this way is slightly more complicated. With an array formula you don’t have to drag and drop cells manually. Steps for pasting as links and transposing in Excel at the same time The formula in the example above looks like this: =OFFSET($B$2,F$1,$E2) That’s why we fix the column but not the row. Similar for the columns: When we pull down the formula, we want the column index to increase.But when copying down the formula, the first row should be fixed with a $-sign. That’s why we don’t add a $-sign in front of the column (“F”). Now, select Copy from the Clipboard group of commands. The process is given below: Step 1: Select the data we have to transpose. We can transpose by using the Ribbon commands. We want this number to increase when we pull the formula to the right hand side. 4 Methods to Transpose Using Excel Paste Shortcut 1. The second part of the OFFSET formula defines, how many rows your target cell is away from the base cell.This base cell should not change when we copy and paste the formula so that we insert the $-signs. In our case, the base cell is the left top corner cell of the range we want to transpose and link.The following number match to the picture above: with ROW() or COLUMN()) but this is the fastest and easiest method. There are certainly more elegant ways (e.g. Roughly said, you define a base cell and tell Excel which value counting from the base cell to return.įirst: Prepare the number 0-2 (the maximum number of rows – 1) on the top and 0 to 1 on the left hand side. The OFFSET function is powerful but unfortunately especially for beginners not very easy to use. Workaround: Use the OFFSET formula for pasting links and tranpose them at the same time