Results 1 to 6 of 6

Thread: CVS file blanks in my data

  1. #1
    Neophyte sfeAnalyst's Avatar
    Join Date
    May 2021
    Posts
    3
    Articles
    0
    Excel Version
    Excel 365

    Unhappy CVS file blanks in my data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,


    I have an issue that I'm stuck with since this 2 days, actually i have a CVS file containing more than 5 millions rows with the following columns :


    Idcode / Years / Months / Companies / Products / Units / sales in $.


    So to analyze this data i used Power Query and i added the table to my data model.


    My problem begins when i added a pivote table and i put in rows "Company + products" and in value "Units or sales in $" in my products i have blanks and i don't know where this blanks came from.


    NB: The CVS file contains clean data that i receive from a big data company.


    Any idea ? any solution ? I'm going crazy


    Tnx by advance
    Last edited by sfeAnalyst; 2021-05-12 at 11:52 PM.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    You'll need to provide a sample data file and workbook so that we can see the problem.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte sfeAnalyst's Avatar
    Join Date
    May 2021
    Posts
    3
    Articles
    0
    Excel Version
    Excel 365
    Hi Tnx for your response,
    I can't share the file because it's on my professional computer i can share shoo screens if needed.
    The fact is that my data is clean, when i inspect it on power query editor by filtering and loading the products column i have no blanks and only the list of my products, the problem comes when i use this data on a pivot table !!!!

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    If you load the result of your Power Query as a table and then use it as a source to create a pivot table, then you need to see exactly which products are experiencing this problem.

    You can filter these rows, anonymize them and make them available here. Before you do that, you should check if the problem still occurs when you copy the filtered data into a new table to create a pivot table.

  5. #5
    Neophyte sfeAnalyst's Avatar
    Join Date
    May 2021
    Posts
    3
    Articles
    0
    Excel Version
    Excel 365
    My file is very heavy soo to investigate if my data contains blanks i added a custum column with the follwing formula :
    = if ([Product] = null) then 1 else 0
    AND I FIND NO BLANKS !!!!! which is very weird :s

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    If you are not able to show something, then it is very hard to help.

    There may be non-displayable characters in the column that you can clean up with a clean text.

    Otherwise, as already explained, load the result of the query as a table to create a pivot table. Then try to identify errors using other criteria and make them available here anonymously. But test before, if the error still happens with this subset.

Tags for this Thread

Posting Permissions

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