none
Power Pivot doesn't regonize sources any longer RRS feed

  • Question

  • Hi

    I have set up a data model that collects data from several flat files (.csv files).

    It has been working for several months - latest at the beginning of June.

    But now Power Pivot don't even recognize the files.

    When I try to establish a new connection to the csv file, then Power Pivot doesn't even recognize the semicolon in the data...

    Something has happened within the last 2-4 weeks...

    What is wrong - and how do I solve it...?

    wkr Jørgen

    Monday, June 29, 2020 1:15 PM

All replies

  • Follow these steps, to find the source data for a pivot table:

    Select any cell in the pivot table.
    On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab).
    In the Data group, click the top section of the Change Data Source command.
    Change Data Source command on Excel Ribbon

    The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. This may be a reference to a sheet and range of cells

    Behind the dialog box, you can see the source range on the worksheet, surrounded by a moving border.

    In the screen shot below, the source data range ends at row 9, and a new record has been added in row 10. That record will not appear in the pivot table, unless the Data Source is adjusted.

    Once you find the source data, you can recognise it.

    Monday, June 29, 2020 1:18 PM
  • What is wrong - and how do I solve it...?

    Seems in row 2 there is an unwanted CR+LF = line break which breaks the file format; you have to remove the line break

    And column 1 looks strange like PP don't recognize the semicolon as column delimiter. You have to check the source file if it contains non-ASCII characters.



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, June 29, 2020 1:23 PM
    Moderator
  • Hi

    Thanks for your reply.

    However I think, that you misunderstand me. I'm using Power Pivot and I am fully aware of where the data file is and what it looks like...

    My problem is that my model in Power Pivot worked fine 3 weeks ago - and now it doesn't... It is the same data files, so nothing has changed here...

    All files are flat files with the correct columns etc... My best guess is that Microsoft had released an update (with a bug)...

    The flat files (unchanged) doesn't even work in a new Power Pivot model anylonger...

    I too finds it strange how the system handles the data in row 2... The file is categorized as semicolon delimited, but Power Pivot doesn't separate the data even though the semicolons are clearly there... (Why the model suddently splits some data in to new columns or rows is a mystery to me too....

    If I could attach a csv file you could test it in Power Pivot to see, if it fails by you as well...

    Wkr Jørgen

    Monday, June 29, 2020 8:45 PM
  • Hi

    Thanks for your reply.

    However I think, that you misunderstand me. I'm using Power Pivot and I am fully aware of where the data file is and what it looks like...

    My problem is that my model in Power Pivot worked fine 3 weeks ago - and now it doesn't... It is the same data files, so nothing has changed here...

    All files are flat files with the correct columns etc... My best guess is that Microsoft had released an update (with a bug)...

    The flat files (unchanged) doesn't even work in a new Power Pivot model anylonger...

    I too finds it strange how the system handles the data in row 2... The file is categorized as semicolon delimited, but Power Pivot doesn't separate the data even though the semicolons are clearly there... (Why the model suddently splits some data in to new columns or rows is a mystery to me too....

    If I could attach a csv file you could test it in Power Pivot to see, if it fails by you as well...

    Wkr Jørgen

    Monday, June 29, 2020 8:46 PM
  • If I could attach a csv file you could test it in Power Pivot to see, if it fails by you as well...

    You have a Microsoft account, so you have a free online storage on OneDrive, where you can upload the file to: https://onedrive.live.com/

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 30, 2020 5:48 AM
    Moderator
  • Here you have a link to the file...

    Please check if you are able to make Power Pivot use the file correctly...

    https://1drv.ms/u/s!AjDbOUXrm6UCiSt6QNNRT5BemArJ?e=B35yMF

    Thanks in advance...

    wkr Jørgen

    Tuesday, June 30, 2020 6:20 AM
  • Excel 365 Pro Plus with PowerPivot and Power Query.
    Works fine with PQ Get & Transform From Text/CSV.
    http://www.mediafire.com/file/31iwrszba9gsuww/06_30_20a.xlsx/file
    http://www.mediafire.com/file/ak4z8vhulmncywh/06_30_20a.pdf/file

    Tuesday, June 30, 2020 4:38 PM
  • Here you have a link to the file...

    That link points to a XLSX file, not the source CSV file.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 1, 2020 6:04 AM
    Moderator
  • Sorry... I can see, that the file has been changed to day by someone...

    I have place two new files (CSV again)...

    I cannot get Power Pivot to recognize them when I add them to the data model via "Home", "From Other Surces", "Text File"...

    Please see if you can get it to work...

    Wkr Jørgen

    Wednesday, July 1, 2020 6:31 AM
  • I have exactly the same problem. Based in Sweden, which may be relevant.

    Microsoft Office Professional Plus 2016. Made a test.txt file:

    column1;column2;column3
    1;2;3

    PowerPivot - Get External Data - From other sources - Text File

    Choose semicolon as separator.

    Unfortunately I cannot attach an image as I am not verified, but the preview shows a single column with all the data.

    No idea what is going on here. I tried the following to no avail:

    • changing the PowerPivot Language till Swedish
    • changing the Locale (under Advanced button) to Swedish
    • changing the character encoding (under advanced button) from ASCI to UTF-8 / unicode

    When using comma as column separator, everything works fine...

    • Edited by Erique1977 Monday, July 6, 2020 8:39 AM
    Monday, July 6, 2020 8:38 AM
  • I have this exact same issue (based in The Netherlands). Semicolon, vertical line, they all fail. The only recognized separator in all cases is the comma, regardless of the delimiter choice one makes.
    Friday, July 10, 2020 11:57 AM