Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jagostinhoCT
Post Partisan
Post Partisan

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

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
v-yulgu-msft
Employee
Employee

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.

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.