+ Reply to Thread
Results 1 to 15 of 15

PowerQuery shows null, CSV files dont have (blanks)

  1. #1
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

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

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

  5. #5
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,353

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  10. #10
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

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

    Thank you very much!
    Attached Files Attached Files

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

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

    I thought you were importing from CSV files?

  12. #12
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    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)

Similar Threads

  1. [SOLVED] Append multiple DBF files in PowerQuery
    By wrybel in forum Excel General
    Replies: 18
    Last Post: 05-24-2020, 11:55 PM
  2. Append multiple DBF files in PowerQuery with with a changing path
    By atrencheva in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2019, 04:11 AM
  3. Restrict Combobox Selection RowSourse Only and Dont allow null
    By sijostephen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2017, 09:38 AM
  4. [SOLVED] conditional formating, I dont want blanks ighlighted
    By watchthisspace in forum Excel General
    Replies: 20
    Last Post: 04-28-2017, 05:12 AM
  5. [SOLVED] Access Query shows (Blanks) but no blanks in data!
    By MissDB in forum Access Tables & Databases
    Replies: 0
    Last Post: 09-08-2016, 07:16 AM
  6. Histogram shows negative value where I dont want it
    By Annatw in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-08-2016, 03:36 AM
  7. Countifs with null or blanks
    By shuddle in forum Excel General
    Replies: 3
    Last Post: 10-14-2011, 02:43 PM

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