I am working with Excel 365. My main worksheet has these fields:
Ref-field1, ref-field2, ref-field3, ... ref-field30, value1, value2, value3 … value13, total
Ref-field1 thru 30 are text fields with various reference data.
In each row, Value1 thru Value 13 have values of either 1 or null, depending of whether the valueN category applies to that row or not.
I want to unpivot this, so that each row has
Ref-field1, ref-field2, ... ref-field30, Attribute (one of value1 thru 13), Value.
Excel’s default option (nulls are omitted) works nicely for this. And I know that the number of rows to expect in the output equals the sum of all value-fields in all rows.
I have done this using the Get & Transform / Power BI functions: I select the columns I want unpivoted, then choose Transform > Unpivot , and then Home > Close & Load.
At this point, Excel creates a new tab in my worksheet, and shows the Queries and Connections panel. The latter shows me that there are 16 errors – and that these are three totally blank rows.
But how I can I find out what actual rows in the main table these errors are coming from?
(I cannot share the actual spreadsheet because it’s got corporate data in it.)