Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |