Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

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

 

Regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

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