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

[DmitryKo]

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.

[DmitryKo]

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

[v-jingzhang]

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]

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]

Any ideas on this?