Forum Discussion

DmitryKo's avatar
DmitryKo
Icon for Helper IV rankHelper IV
3 years ago

Extra rows when importing from Excel (line breaks within cells)

Importing Excel file as datasource into PBI Desktop.

Some cells in Excel contain multi-line text that includes line breaks; such cells get split during import and imported as more than one row (there isn't a separate step for this - this already happens after "Navigation" step).

This is unwanted behavior; we want each row - as it is in the Excel file - to become exactly one row in PBI table.

Is there a M option or function to parse it this way? UI provides nothing that works.

Need Help

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi DmitryKo 

     

    Please check whether the line break in your Excel file is ASCII 10? If it is ASCII 10, it can be remained after importing data into PQ in my test.

    If it is not, you may need to find out which character the line break is and replace it with ASCII 10. The following link may be helpful: worksheet function - How to search for newline or linebreak characters in Excel? - Super User

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • DmitryKo's avatar
      DmitryKo
      Icon for Helper IV rankHelper IV
      3 years ago

      So power query is incapable of correctly importing an .xlsx file, incapable of correctly determining data rows?

      As mentioned above, I want a really simple thing: what is "row" in Excel should become row in power query-imported table. Regardless of any special characters it might or might not have.

  • DmitryKo's avatar
    DmitryKo
    Icon for Helper IV rankHelper IV
    3 years ago

    I get it that in 2023 power bi is incapable or properly importing excel file, which is sad to say the least.