carriage return creating new record in power bi desktop

[hardikmehta112]

I am trying to load csv file from azure portal, In csv file one text column has (new line character) in it.

Now When I load this file in Power BI then carriage return created new record from that part and thus Power BI gives data type mis match error.

I have tried to replace carriage return  chaacter with "." in advance query editor but its not working. Any one know how I can resolve this issue 
See below replace code and screen shot for reference.

Replace code: 
let
Source = AzureStorage.Blobs("AzureStoreURL"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Demo_Journal_Header", each #"Transform File from Demo_Journal_Header"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Demo_Journal_Header"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Demo_Journal_Header", Table.ColumnNames(#"Transform File from Demo_Journal_Header"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ID", type text}, {"OWNERID", type text}, {"ISDELETED", type logical}, {"NAME", type text}, {"CURRENCYISOCODE", type text}, {"RECORDTYPEID", type text}, {"CREATEDDATE", type datetime}, {"CREATEDBYID", type text}, {"LASTMODIFIEDDATE", type datetime}, {"LASTMODIFIEDBYID", type text}, {"SYSTEMMODSTAMP", type datetime}, {"LASTACTIVITYDATE", type any}, {"LASTVIEWEDDATE", type any}, {"LASTREFERENCEDDATE", type any}, {"demo__ACC_CURRENCY__C", type text}, {"demo__ACC_PERIOD_END_DATE__C", type date}, {"demo__ACCOUNTING_PERIOD__C", type text}, {"demo__AGE__C", Int64.Type}, {"demo__ALT_CURRENCY__C", type text}, {"demo__APPROVAL_TYPE__C", type any}, {"demo__APPROVER1_ASSIGN_TYPE__C", type text}, {"demo__APPROVER1_GROUP_ID__C", type any}, {"demo__APPROVER1_NAME_UTE__C", type any}, {"demo__APPROVER1_UTE__C", type any}, {"demo__APPROVER1__C", type any}, {"demo__APPROVER_ASSIGN_TYPE__C", type text}, {"demo__APPROVER_GROUP_ID__C", type text}, {"demo__APPROVER_NAME_UTE__C", type text}, {"demo__APPROVER_UTE__C", type text}, {"demo__APPROVER__C", type text}, {"demo__BUSINESS_WORKDAY__C", Int64.Type}, {"demo__CLASSIFICATION__C", type text}, {"demo__COMMENTS__C", type any}, {"demo__DESCRIPTION__C", type text}, {"demo__ERP_COUNT__C", Int64.Type}, {"demo__ERP_ERROR_MESSAGE__C", type text}, {"demo__ERP_LINE_ITEM_COUNT__C", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","#(cr)",".",Replacer.ReplaceText,{"demo__DESCRIPTION__C"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(cr)",".",Replacer.ReplaceText,{"demo__ERP_REASON_CODE__C"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Source.Name"})
in
#"Removed Columns"

Screen shot.

[v-qiuyu-msft]

Hi @hardikmehta112,

How about the result if you use TEXT/CSV connector in Power BI desktop to get data from CSV file directly? 

i don't have Azure BLOB storage to test it on my side, I just use TEXT /CSV connector to get data from CSV file directly in Power BI desktop version 2.66.5376.2161 64-bit (February 2019) but not reproduce the issue. 

Best Regards,
Qiuyun Yu 

[hardikmehta112]

Hi @v-qiuyu-msft ,

Thanks for reply,

Actually that 's the requirement to get data from Azure, but I also try with TEXT/CSV connector in Power BI desktop but getting same error.

Thanks,

Hardik Mehta

[v-qiuyu-msft]

Hi @hardikmehta112 ,

Is it possible for your to share the Excel file with us? If it contains sensitive data, you can upload it to your OneDrive and send the share link to me via private message. 

Best Regards,
Qiuyun Yu