power bi csv file import query works in desktop but fails in dataflow

[newby_guy]

I have a power query script that imports a csv file (which is tab delimited with "" text qualifiers) from a SharePoint folder - it works fine when I am using the desktop version of Power BI but when I use the same script in Power Query in a Dataverse dataflow it still imports the file but the contents are garbled (all of the header names are in a single column with null entries and there are multiple columns with random characters) - kinda looks like the wrong file encoding is being used but there are no steps in the script that specifiy this? This is the query (with fictitiuos urls and folders):

let

    // Gets all files from SharePoint Teams Site

    Source = SharePoint.Files(" https://random.sharepoint.com/sites/randomsite/", [ApiVersion = 15]),

    // Filter to the folder that should contain the CSV files containing dashboard data.

    #"Filter To Appropriate Folder" = Table.SelectRows(Source, each Text.Contains([Folder Path], "/randomfolder” BI")),

    // Ensures only CSVs are attempted to be read

    #"Filter To Only .CSV" = Table.SelectRows(#"Filter To Appropriate Folder", each [Extension] = ".csv"),

    #"Get Latest Data Item" = Table.SelectRows(#"Filter To Only .CSV", let latest = List.Max(#"Filter To Only .CSV"[Date created]) in each [Date created] = latest),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Get Latest Data Item", each [Attributes]?[Hidden]? <> true),

    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),

    #"Filtered Hidden Files3" = Table.SelectRows(#"Filtered Hidden Files2", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files3", "Transform File", each #"Transform File"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Filtered Hidden Files4" = Table.SelectRow

s(#"Renamed Columns1", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files4", "Transform File (2)", each #"Transform File (2)"([Content])),

    #"Removed Other Columns2" = Table.SelectColumns(#"Invoke Custom Function2", {"Transform File (2)"}),

    #"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)")))

in

    #"Expanded Table Column2"

the garbled file contents look like this:

[Omid_Motamedise]

Hi @newby_guy 

It might be because of type of your CSV file, Please In the first line, use the following code instead and let me know the change in the result

Csv.Document([Content],
    [Delimiter = "#(tab)", Columns = 10, Encoding = TextEncoding.Utf8, QuoteStyle = QuoteStyle.Csv]
)

[newby_guy]

Omid,

thank you for the suggestion - I added the code to the Transform File(2) script:

let

    Source = (Parameter1 as binary) => let

    //Source = Csv.Document(Parameter1,[Delimiter=",", Columns=31, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    Source = Csv.Document([Content],

    [Delimiter = "#(tab)", Columns = 31, Encoding = TextEncoding.Utf8, QuoteStyle = QuoteStyle.Csv]

),

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

in

    #"Promoted Headers"

in

    Source

and now my main script has an error at the 2nd last step (#"Removed Other Columns2") the single row of the table has an [Error] and when I click on that it shows this:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Details

Reason = Expression.Error

ErrorCode = 10282

[newby_guy]

Transform File (2):

let

    Source = (Parameter1 as binary) => let

    Source = Csv.Document(Parameter1,[Delimiter=",", Columns=31, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

in

    #"Promoted Headers"

in

    Source

Sample File (2):

let

    // Gets all files from SharePoint Teams Site

    Source = SharePoint.Files"https://random.sharepoint.com/sites/randomsite/", [ApiVersion = 15]),

    // Filter to the folder that should contain the CSV files containing dashboard data.

    #"Filter To Appropriate Folder" = Table.SelectRows(Source, each Text.Contains([Folder Path], "/randomfolder”)),

    // Ensures only CSVs are attempted to be read

    #"Filter To Only .CSV" = Table.SelectRows(#"Filter To Appropriate Folder", each [Extension] = ".csv"),

    #"Get Latest Data Item" = Table.SelectRows(#"Filter To Only .CSV", let latest = List.Max(#"Filter To Only .CSV"[Date created]) in each [Date created] = latest),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Get Latest Data Item", each [Attributes]?[Hidden]? <> true),

    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),

    #"Filtered Hidden Files3" = Table.SelectRows(#"Filtered Hidden Files2", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files3", "Transform File", each #"Transform File"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    Navigation1 = #"Renamed Columns1"{0}[Content]

in

    Navigation1

[jennratten · Accepted solution]

Hello @newby_guy - Please try this sample script below by adding a blank query to your dataflow and using it as the script.  This include corrections to the encoding as well as cleaning it up a bit to make it more performant and readable. Also, since you mentioned that you are getting a file (as opposed to compiling data from multiple files, you omit all of the other components such as the function, parameter, sample, transform from sample. 

Explanation of the changes to Csv.Document...

• In your message you mentioned needing to query a tab delimited file with "" text qualifiers.  
• In the sample file script provided, the specifications below are present, which essentially says you are needing a comma delimited file for MS Windows with no text qualifiers.  This is why you got the result that you did in the dataflow.
  • Delimiter = ","
  • Encoding=1252
  • QuoteStyle=QuoteStyle.None
• The revised script provided includes these specifications instead, to accommodate your requirements.
  • Delimiter = "#(tab)"
  • Encoding = TextEncoding.Utf8
  • QuoteStyle = QuoteStyle.Csv

Explanation of other optimizations:

• Duplicative [Attributes]?[Hidden]? <> true were removed.
• Combining the content for all resulting files was removed.
• Extension and Folder Path were made a bit more resilient by making them case insensitive.
• An additional line (commented out) was provided for a filter on file name if you so choose to use it.

Complete script

let
  Source = SharePoint.Files("https://random.sharepoint.com/sites/randomsite/", [ApiVersion = 15]),
  FilteredRows = Table.SelectRows(Source, each 
    [Attributes]?[Hidden]? <> true 
    and Text.Contains ( [Extension], ".csv", Comparer.OrdinalIgnoreCase ) 
    and Text.Contains([Folder Path], "/randomfolder BI", Comparer.OrdinalIgnoreCase ) 
//    and Text.Contains([Name], "sample_tab_delimited", Comparer.OrdinalIgnoreCase ) // file name filter
    ),
// Get the latest data item
   LatestDataItem = Table.SelectRows(FilteredRows, let latest = List.Max(FilteredRows[Date created]) in each [Date created] = latest),
  Binary = LatestDataItem{0}[Content],
  Content = Csv.Document(Binary,[Delimiter = "#(tab)", Encoding = TextEncoding.Utf8, QuoteStyle = QuoteStyle.Csv]),
  PromoteHeaders = Table.PromoteHeaders(Content, [PromoteAllScalars = true])
in 
    PromoteHeaders

[newby_guy]

Hi,

Thank you @jenratten - for anyone else who stumbles across this issue the csv was a report from some SQL server somwhere and person unknow had altered the type of csv from comma delimited to tab delimited (I did not spot that this detail was described in the other query). In any case you script is much cleaner and easier to follow.

[jennratten]

You're very welcome!

[jennratten]

Hello @newby_guy - can you please post the scripts for #"Transform File (2)" and #"Sample File (2)"?