Forum Discussion
Pandas dataframe passed to Python script is corrupted
The response from Microsoft is that Power BI was not designed to handle embedded newlines - see here:
So the only solution is to "sanitize" the data before invoking Python or R.
To do that, use the Table.ReplaceValue() in M Query, see below. All these actions can be invoked from the GUI.
#"Duplicated Column" = Table.DuplicateColumn(#"some-previous-step", "Defect", "Defect - Copy")
// Santize the new column
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","#(lf)","___",Replacer.ReplaceText,{"Defect-Copy"}),#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(cr)","___",Replacer.ReplaceText,{"Defect-Copy"}),
// Must remove the original bad column
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Defect"}),
// Rename the new column
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Defect-Copy", "DefectSanitized"}})
To add an interesting (and relevant) data point:
I fed the same table into an R script, using this simple-minded script (which does not change any data):
new_ds <- dataset
new_ds$DefectCopy <- new_ds$Defect
And the resulting dataset is similar:
1. The field 'Defect' now contains only the data before the first newline
2. New "dummy" rows are added after this row, each containing the part from the previous newline to the next.
3. In these "dummy" rows, all other columns, including 'Defect', are empty
So this adds weight to my theory that the Power Bi code that parses the table and generates a Python/R dataframe cannot handle newlines embedded in text columns.