Forum Discussion
too many columns
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
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
- Remove all columns except the Content (containing the Binaries) and the Date Created columns
- Combine the Binaries
- 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"})
3 Replies
Hi Anonymous,
Here is a similar thread which has provided a valid solution. Please have a try.
Regards,
Yuliana Gu
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?
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
- Remove all columns except the Content (containing the Binaries) and the Date Created columns
- Combine the Binaries
- 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"})
