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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.