Forum Discussion
How to Ignore Csv quoted line breaks
Hi,
I have a Power BI Report that connected to a Sharepoint folder with many CSV files. And I know I have a quoted line break issue and need to find out a way to ignore the quoted line break. I've done some research and need to handle it by using QuoteStyle.Csv.
However, I'm using a different way to append all the files into one dataset instead of using the default "Combine&Edit". I tried to add "QuotaStyle.Csv" in my code (As highlighted) but still didn't work. Not sure whether should handle it at "Table.ExpandTableColumn" step and how. Any idea would be appreciated.
- Why are you not using the Combine feature of Power Query?
- Don't use SharePoint.Files here. It looks like you are getting files from 1 folder. Change SharePoint.Files() in the source line to SharePoint.Contents(). Navigate to the folder you need, then do a combine (or filter further to just keep the CSVs you need, then combine).
3 Replies
- edhans
Community Champion
- Why are you not using the Combine feature of Power Query?
- Don't use SharePoint.Files here. It looks like you are getting files from 1 folder. Change SharePoint.Files() in the source line to SharePoint.Contents(). Navigate to the folder you need, then do a combine (or filter further to just keep the CSVs you need, then combine).
Hi edhans ,
1. I've watched a video (https://www.youtube.com/watch?v=3GIz50pftZ0) and found out this way is much more clean and simple.
2. I can do that, but it will use Combine which I'm trying to avoid. Though I know it can solve my issue here. Anyway, it's an option. Thanks~
Wondering is there any other fix can be done without changing my Source line?
let Source = Table.SelectRows(SharePoint.Files("https://company.sharepoint.com/sites/department", [ApiVersion = 15]),each Text.Contains([#"Folder Path"],"folder_name")), #"Removed Other Columns" = Table.TransformColumns(Table.SelectColumns(Source,{"Content", "Name"}),{"Content",each Table.SelectRows(Table.PromoteHeaders(Table.Range(Csv.Document(_,[Delimiter=","]),3)),each [UnitID_1]<>"")}), #"Expanded Content" = Table.ExpandTableColumn(#"Removed Other Columns", "Content", {"A", "B", "C", "D", "E", "F"}, {"A", "B", "C", "D", "E", "F"}) in #"Expanded Content"
