Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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

 

 

Need Help

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        Hi,

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.
  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.