Forum Discussion

MillyF's avatar
MillyF
Regular Visitor
4 years ago

Trouble connecting to data source (Excel file, not in table format)

I manage an Excel file that contains 222 columns and it currently has around 2000 rows. The file size is around 3.39MB. It is not in Table format and it has many custom formulas. Power BI cannot connect to this file unless I copy and paste all data into a whole new Excel file and connect to that new file then. It is not an optimal solution as this file gets updated daily and I would like to avoid the daily copy-paste. I have tried to select many columns to the right of my data and delete them (maybe there are additional columns that "contain data"?) but it still does not work- Power BI connects to the source but it only shows Columns and no additional data. Please help.

 

 

Need Help

8 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi MillyF ,
    one general limitation is that you cannot connect to xls files from Power BI, just xlsx. 
    Apart from that, I'm not sure that you're doing your transformations at the right place: You're showing a picture from when the data is loaded into the data model already.
    But to reduce the columns imported, you need to do that in the query editor (Transform data) instead.

    • MillyF's avatar
      MillyF
      Regular Visitor
      4 years ago

      Thank you, this has been helpful to remove the columns I do not want to load. I am still facing an issue, though, as noted in my comment above.

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi MillyF ImkeF ,
        It looks like there are some errors in your columns that you are showing such as Billing Start.  Maybe look into your columns.
        Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi MillyF ,
    Not sure what you mean by "I have tried to select many columns to the right of my data and delete them (maybe there are additional columns that "contain data"?)" 

    And what do you mean that it cannot connect?

    I do know that Power Query does better with fewer columns.  Is it possible to divide the data onto different tabs? Also I might try to copy everything to a new workbook including the formulas in case that is the issue.

     

    In any case ImkeF is a whiz with Power Query, maybe she can help!


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

    • MillyF's avatar
      MillyF
      Regular Visitor
      4 years ago

      I have been able to connect to the data source but I am still running into an issue where data under each header is not populating as soon as I close Power Query Editor.

      I see data in the Power Query Editor, but it is not populating as soon as I close it.

       

       

       

       

       

       

       

       

       

  • MillyF's avatar
    MillyF
    Regular Visitor
    4 years ago

    Hi Nathaniel, I counted how many columns I have in the Excel file and there were 222. I see Power BI "thinking" that there are around 240 or so. I cannot figure out why that would be the case. I cannot move that file onto a new file because then many formulas would break in other tabs.