Forum Discussion

MVP

Feb 15, 2017

Get & Transform - solving all sorts of Excel issues

Some may see Get & Transform (Power Query) simply as a brilliant way of connecting to multiple data sources and loading that data into a Microsoft Power BI development environment (Excel Power...

BI & Data Analytics

MVP

Feb 21, 2017

Thanks Guy

Ricardo Figueroa

Copper Contributor

Jan 06, 2018

Thanks for the example. I was wondering whether anyone have dealt with the Alt Enter situation when importing a large csv file? I was looking to see whether there is a way for the Get and Transform procedure to replace the end of line values prior to importing the file.

Wyn Hopkins
MVP
Jan 08, 2018
Hi Ricardo Figueroa

Similar to the Split Column by LineFeed you can use Transform > Replace Values and again choose LineFeed

Under advanced options choose Line Feed and then put a space in the replace with box
- Ricardo Figueroa
  Copper Contributor
  Jan 08, 2018
  Wyn,
  
  Thanks so much for your prompt response. This works well when the information is already in Excel... but I have not been able to find a solution for the case when importing the information from a csv file without having to load it into a worksheet.
  
  In the following example, I am trying to specify at the source level how to manage the line breaks but it does not give the option to replace values within a specific field. Since this is a large file, I would like to avoid the need to import the information into Excel and then replace values (this is doable through the procedure you explained).
  
  I will keep looking for a solution with Get and Transform.
  
  Update. I checked the other option for line breaks the one that reads to ignore quoted line breaks. It worked... it fixed the issue without the need to replace the line breaks with any other value.

Resources