Excel show empty column but when downloaded/referred it's ignored

%3CLINGO-SUB%20id%3D%22lingo-sub-2433014%22%20slang%3D%22en-US%22%3EExcel%20show%20empty%20column%20but%20when%20downloaded%2Freferred%20it's%20ignored%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2433014%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22yangwei_1-1623283578612.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F287735i8B6CB892F0C903EF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22yangwei_1-1623283578612.png%22%20alt%3D%22yangwei_1-1623283578612.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20simplified%20version%20of%20my%20sheet.%20It%20was%20one%20of%20the%20excel%20sheets%20that%20my%20ex-colleague%20created.%3C%2FP%3E%3CP%3ECurrently%2C%20there%20are%20two%20behaviours%20that%20I%20couldn't%20explain%20and%20it's%20confusing%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EWhen%20I%20refer%20to%20this%20sheet%20(from%20other%20sheets)%2C%20it%20always%20pull%20the%20range%20from%20column%20B%20(instead%20of%20column%20A)%3C%2FLI%3E%3CLI%3EWhen%20I%20download%20this%20sheet%20as%20CSV%2C%20they%20look%20like%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EFY%2C%0A1%2CA%0A2%2CA%0A...%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20we%20leave%20the%20first%20column%20empty%2C%20the%20correct%20CSV%20should%20be%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%2CFY%2C%0A%2C1%2CA%0A%2C2%2CA%0A...%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20here%20know%20what%20might%20cause%20this%20behaviour%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2433014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2433130%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20show%20empty%20column%20but%20when%20downloaded%2Freferred%20it's%20ignored%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2433130%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EExcel%20assumes%20that%20columns%20with%20NO%20data%20at%20all%20are%20not%20part%20of%20the%20data%20set.%3CBR%20%2F%3EIf%20you%20want%20to%20export%20as%20%2CFY%2C%20then%20you%20need%20to%20put%20%3D%22%22%20in%20each%20cell%20in%20the%20first%20column.%20This%20is%20letting%20Excel%20know%20that%20these%20cells%20are%20blank%20but%20included%20in%20the%20data%20set.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2433152%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20show%20empty%20column%20but%20when%20downloaded%2Freferred%20it's%20ignored%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2433152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064156%22%20target%3D%22_blank%22%3E%40StoneKiwi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20your%20response.%3C%2FP%3E%3CP%3EI%20am%20not%20quite%20sure%20about%20that%20but%20when%20I%20have%20this%20(I%20didn't%20put%20%22%22)%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22yangwei_0-1623288609021.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F287749iD21A0813FA4E572D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22yangwei_0-1623288609021.png%22%20alt%3D%22yangwei_0-1623288609021.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhat%20I%20got%20when%20I%20downloaded%20is%20as%20expected%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%2CFY%2C%0A%2C1%2CA%0A%2C2%2CA%0A...%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

yangwei_1-1623283578612.png

 

This is a simplified version of my sheet. It was one of the excel sheets that my ex-colleague created.

Currently, there are two behaviours that I couldn't explain and it's confusing

 

  • When I refer to this sheet (from other sheets), it always pulls the data starting range from column B (instead of column A)
  • When I download this sheet as CSV, they look like 

 

FY,
1,A
2,A
...

 

If we leave the first column empty, the correct CSV should be

 

,FY,
,1,A
,2,A
...

 

 

1. Does anyone here know what might cause this behaviour?

2. With power query, is there any way to pull my data by ignoring the first column which is empty

2 Replies
Hi,

Excel assumes that columns with NO data at all are not part of the data set.
If you want to export as ,FY, then you need to put ="" in each cell in the first column. This is letting Excel know that these cells are blank but included in the data set.

@StoneKiwi 

Appreciate your response.

I am not quite sure about that but when I have this (I didn't put "") 

yangwei_0-1623288609021.png

What I got when I downloaded is as expected:

 

,FY,
,1,A
,2,A
...