Dataflow identifies hard return as a record delimiter

Thanks for your response! Source Query and Source Data are Identical.

I walked through the same process this morning, and uncovered slightly different, though mostly the same results.

Building the query again in Desktop PowerQuery and then in the DataFlow, I noticed this may have something to do with the "File origin" when combining files.

When beginning the process of combining files in Power Query, the default "File origin" selection is "1252: Western European (Windows)"

Beginning the process of combining files in DataFlow, the default "File origin" selection is "65001 Unicode (UTF-8)."

The first build (6/24/2024) in PowerQuery used "File origin" 1252: Western European (Windows), and continues to work as expected, ignoring the hard return.

Testing both this morning, I found that PowerQuery is incapable of ignoring that hard return at all, while DataFlow has flipped and now ignores it in 1252, as it did originally in Power Query.

Here are the steps from the 5 different scenarios with two different "File origin" selections.

0riginal  (6/24/2024) = PowerQuery using File Origin > 1252: Western European (Windows) - WORKS

let
    Source = SharePoint.Files("https://MYSITE.sharepoint.com/sites/InformationSystems/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://MYSITE.sharepoint.com/sites/InformationSystems/Shared Documents/DataStore/Therap/External Data Feed/Individual Demographics/Details/")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)")))
in
    #"Expanded Table Column1"

Test Scenario 1 = DataFlow using File Origin > 1252: Western European (Windows) - WORKS

let
  Source = SharePoint.Files("https://MYSITE.sharepoint.com/sites/InformationSystems", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source, each [Folder Path] = "https://MYSITE.sharepoint.com/sites/InformationSystems/Shared Documents/DataStore/Therap/External Data Feed/Individual Demographics/Details/"),
  #"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),
  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file (3)", each #"Transform file (3)"([Content])),
  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file (3)"}),
  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file (3)", Table.ColumnNames(#"Transform file (3)"(#"Sample file (3)")))
in
  #"Expanded table column"

Test Scenario 2 = DataFlow using File Origin > 65001 Unicode (UTF-8) - DOES NOT WORK

let
  Source = SharePoint.Files("https://MYSITE.sharepoint.com/sites/InformationSystems", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source, each [Folder Path] = "https://MYSITE.sharepoint.com/sites/InformationSystems/Shared Documents/DataStore/Therap/External Data Feed/Individual Demographics/Details/"),
  #"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),
  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file (4)", each #"Transform file (4)"([Content])),
  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file (4)"}),
  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file (4)", Table.ColumnNames(#"Transform file (4)"(#"Sample file (4)")))
in
  #"Expanded table column"

Test Scenario 3 = PowerQuery using File Origin > 1252: Western European (Windows) - DOES NOT WORK

let
    Source = SharePoint.Files("https://MYSITE.sharepoint.com/sites/InformationSystems", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Folder Path] = "https://MYSITE.sharepoint.com/sites/InformationSystems/Shared Documents/DataStore/Therap/External Data Feed/Individual Demographics/Details/"),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)")))
in
    #"Expanded Table Column1"

Test Scenario 4 = PowerQuery using File Origin > 65001 Unicode (UTF-8) - DOES NOT WORK

let
    Source = SharePoint.Files("https://MYSITE.sharepoint.com/sites/InformationSystems", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Folder Path] = "https://MYSITE.sharepoint.com/sites/InformationSystems/Shared Documents/DataStore/Therap/External Data Feed/Individual Demographics/Details/"),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)")))
in
    #"Expanded Table Column1"