too many columns

[Anonymous]

Getting an error in some of the rows (not all), after expanding a Custom column to extract the contents.

The error reads

"DataFormat.Error: There were more columns in the result than expected.
Details:
Count=1"

Here is a partial copy/paste from the Advanced Editor.

   #"Filtered Rows - Views2-5.txt" = Table.SelectRows(#"Filtered Rows - Online Audits folder", each [Extension] = ".txt" and [Name] = "Views2-5.txt"),
    #"Changed Type - Date" = Table.TransformColumnTypes(#"Filtered Rows - Views2-5.txt",{{"Date created", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type - Date", "Custom", each Csv.Document([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Custom.Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Content", "Extension", "Date accessed", "Date modified", "Attributes"})
in
    #"Removed Columns"

The other rows that are not producing warnings are like this

\\ct06\01-Models\261SVS-MAIN-CENTRAL-2016.rvt 261SVS-MAIN-CENTRAL-2016 Drafting Blank 100 Views Detail Drafting View 0 0 0 0 0

Thank you

J

[v-yulgu-msft]

Hi @Anonymous,

Here is a similar thread which has provided a valid solution. Please have a try.

Regards,

Yuliana Gu

[Anonymous]

thanks @v-yulgu-msft.

I had looked at that thread before. It did not work for me.

Having a look at the raw txt I find that the line where it breaks has some commas and I want it to split by Tab.

I think it is on this line of the code I need to set the delimiter but I am not familiar with the function syntax and do not know how to add that.

= Table.ExpandTableColumn(#"Filtered Rows", "Custom", {"Column1"}, {"Custom.Column1"})

Can you help me?

[Anonymous · Accepted solution]

I think I have sorted it, but it feels like cheating.

Here is what I did.

One of the key things was to keep the Date Created column from the source file, which I would loose if I combined the Binaries.

But....I found that if I combine the binaries and then go back and edit the code in the Advanced Editor and add the "Date Created" column to the columns to keep list, I can still have it.

When I combine the binaries it reads correctly, I mean, it does not split the columns by comma but rather by Tab, which is the correct way.

Does this make sense? Will I run into problems further ahead with this kludge?

In short

1. Remove all columns except the Content (containing the Binaries) and the Date Created columns
2. Combine the Binaries
3. Edit the code using the Advanced Editor and add the name of the column e.g. “Date Created” to the list of columns to keep.

= Table.SelectColumns(#"Invoke Custom Function1", {"Date created", "Transform File from CTBIMAudits - Warnings"})