How to remove intermittent blanks from a CSV import, without removing data in adjacent columns?

[Anonymous]

Hi all,

I'm importing a CSV which contains intermittent blanks in some columns, which don't align with blanks in other columns. Therefore in Power Query, if I remove blanks in one column it will 'remove' data in an adjacent column:

So in the picture above, if I removed blanks 1 and 2, I'd also lose the values in 1.2 and 1.3.

Any suggestions as to how I could remove blanks and retain adjacent values?

Thanks in advance

[v-juanli-msft]

Hi @Anonymous 

Create a new blank query, 

copy the source file path and paste here, 

keep only "cl2" column, 

remove blank row for "cl2" column,

add an index column from 1

in Table, remove "cl2" column, 

add an index column from 1,

merge queries, expand "cl2" column

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous 

Create a new blank query

copy source file path from "Table" and paste here,

then remove other columns, only keep "cl2",

remove blank rows for "cl2" column,

add index column from 1

Then in "Table", 

remove "cl2" column,

add index column from 1

merge queries

expand "cl2" column

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I suggest that you plug in the blank cells with the value appearing from above.  Right click on the second column's heading and select Fill > Down.

Hope this helps.

[Anonymous]

Hi, thanks for that suggestion - I probably should have mentioned that I want to put this information in a table, so don't want duplicates showing.

[Ashish_Mathur]

Hi,

When you drag column 2 to a Table visual, you will only see unique entries.

[Anonymous]

Yes, with gaps in there when done with columns 1 and 3. I'm trying to create a table with no gaps between fields

[Ashish_Mathur]

Hi,

Once you fill the gaps in column 2 (as described above) and drag that column to the visual, you will not face any problem.