Our first task will be to replace all the empty value cells with zeroes. Empty cells will be skipped in the next step when we transpose the table. Since we don’t want chronological gaps in our data, we need to occupy the empty cells with a value.
NOTE: In Power Query, an empty cell is labeled as a “null” cell. This is just a fancy way to say “empty” in Power Query.
We have 12 columns of monthly data to replace nulls with zeroes.
Click the header of the “Jan” column, scroll to the right, hold CTRL then click the header of the “Dec” column.
With the 12 month columns highlighted, select Home (tab) -> Transform (group) -> Replace Values.
In the Replace Values dialog box, type “null” (no quotation marks) in the Value To Find field, and a “0” (no quotation marks) in the Replace With field and click OK.
All the “null” cells have been replaced with zeroes.