Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
= Table.SelectColumns(#"Invoke Custom Function1", {"Date created", "Transform File from CTBIMAudits - Warnings"})
Hi @jagostinhoCT,
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
= Table.SelectColumns(#"Invoke Custom Function1", {"Date created", "Transform File from CTBIMAudits - Warnings"})
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |