+ Reply to Thread
Results 1 to 15 of 15

PowerQuery shows null, CSV files dont have (blanks)

  1. #1
    Registered User
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    73

    PowerQuery shows null, CSV files dont have (blanks)

    Hello friends, hope all is well!

    I placed multi CSV workbooks in a folder, and PowerQuery to compile them in one sheet.
    All went well. What is driving me nuts, is that I found null cells, even though I filled all the empty cells with zero in the original CSV files.
    i.e. when going to the CSV files and I filter I wont find (blanks).

    Why is this happening, and how to fix it, I am afraid to delete the nulls in PQ editor and end up erasing important data.

    Please help and thank you so much in advance

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,955

    Re: PowerQuery shows null, CSV files dont have (blanks)

    Attach one of your CSV files which is displaying this behaviour, and post your query code.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    73

    Re: PowerQuery shows null, CSV files dont have (blanks)

    I actually want to attach, and I do most of the time. but there many workbooks, and I am not sure which one to attach. I was thinking what does one check for: formatting correction, header spelling, spaces find, replace "-" with 0, etc. is there like a list of things one should do to see what went wrong?
    Thank you

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,955

    Re: PowerQuery shows null, CSV files dont have (blanks)

    Let's start with your code, and any one of your source files.

  5. #5
    Registered User
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    73

    Re: PowerQuery shows null, CSV files dont have (blanks)

    there are no codes. it is PowerQuery the Imports Multiple CSV Files from a Folder.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,618

    Re: PowerQuery shows null, CSV files dont have (blanks)

    Open PQ and go to the Advanced Editor - copy and paste the code that appears there to your next post here.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    73

    Re: PowerQuery shows null, CSV files dont have (blanks)

    Please Login or Register  to view this content.
    Last edited by saudi_red_neck; 11-11-2019 at 06:56 AM. Reason: brackets

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,955

    Re: PowerQuery shows null, CSV files dont have (blanks)

    You're not making this easy to help you. Please also attach at least one sample source file (preferably several), as I requested (preferably including one of the files which is causing the null value issue you describe).

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,260

    Re: PowerQuery shows null, CSV files dont have (blanks)

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  10. #10
    Registered User
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    73

    Re: PowerQuery shows null, CSV files dont have (blanks)

    Thank you very much!
    Attached Files Attached Files

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,955

    Re: PowerQuery shows null, CSV files dont have (blanks)

    I thought you were importing from CSV files?

  12. #12
    Registered User
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    73

    Re: PowerQuery shows null, CSV files dont have (blanks)

    I am, I changed format because when I shared the sample it was over 2MB in size.

  13. #13
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,260

    Re: PowerQuery shows null, CSV files dont have (blanks)

    Cannot replicate the issue with the file you supplied. However, have you tried to use the replace function built into PQ. On the Transform tab, click on Replace. Replace all nulls with zero.


    Please Login or Register  to view this content.
    Last edited by alansidman; 11-12-2019 at 05:35 AM.

  14. #14
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,955

    Re: PowerQuery shows null, CSV files dont have (blanks)

    I'm pretty confident it will be related to inconsistent column headers in your source CSV files. Which is why I was asking for some samples of the actual source files...

    You can delete most of the rows in a few CSV files and upload, or you can do some checking to make sure your column headers are consistent across all CSV files.

  15. #15
    Registered User
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Excel 2013 Pro Plus
    Posts
    73

    Re: PowerQuery shows null, CSV files dont have (blanks)

    You are all awesome! Thank you. it worked. inconsistent column headers was where I went wrong. Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1