cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: too many columns

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"})

View solution in original post

3 REPLIES 3
Microsoft v-yulgu-msft
Microsoft

Re: too many columns

Hi @jagostinhoCT,

 

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

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: too many columns

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?

 

 

Re: too many columns

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"})

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,758)