With the 12 month columns highlighted, select Home (tab) -> Transform (group) -> Replace Values. We have 12 columns of monthly data to replace nulls with zeroes.Ĭlick the header of the “Jan” column, scroll to the right, hold CTRL then click the header of the “Dec” column. This is just a fancy way to say “empty” in Power Query. NOTE: In Power Query, an empty cell is labeled as a “null” cell. Since we don’t want chronological gaps in our data, we need to occupy the empty cells with a value. Empty cells will be skipped in the next step when we transpose the table. Our first task will be to replace all the empty value cells with zeroes. The first thing we will do is to select the Name field from the Query Settings -> Properties panel on the right and change the name to something more meaningful, like “TableFinal”. The Power Query Editor is an entire suite of tools used to fix virtually any data irregularity or data transformation issue. The data will be sent to the Power Query Editor for further processing. Also, make sure the “My table has headers” option is selected then click OK. Provided there are no completely blank rows or columns in the data, the Create Table dialog box’s selection range should be correct. The Power Query suite of tools ( located in the “Get and Transform” group on the Data tab) will allow us to quickly and easily correct this data.Ĭlick anywhere in the data ( anywhere in A3:N12) and select Data (tab) -> Get & Transform Data (group) -> From Table/Range. To solve this issue, we will need to “zero fill” all the empty cells in the range of numbers. One of the issues with traditional transpose strategies is wherever an empty cell exists, no row will be created for that App/Type combination. 12 rows for “ WenCaL/Returns” ( Jan…Dec), etc.12 rows for “ WenCaL/Volume” ( Jan…Dec).We want to transpose the monthly data and combine it with the App/Type data. Our dataset contains sales for a year grouped by month ( blue), and each row represents an App/Type grouping ( yellow). Prior to 2016, the tools were available via a free download as part of the Power Query toolset. The “Get and Transform” tools have been widely available since Excel 2016.
0 Comments
Leave a Reply. |