• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

DataFormat.Error: There were more columns in the result than expected.|Power Query|Excel Forum|My Online Training Hub

You are here: Home / DataFormat.Error: There were more columns in the result than expected.|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryDataFormat.Error: There were more c…
sp_PrintTopic sp_TopicIcon
DataFormat.Error: There were more columns in the result than expected.
Avatar
Sebastian Fuentes

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 8, 2021
sp_UserOfflineSmall Offline
1
April 8, 2021 - 6:54 am
sp_Permalink sp_Print

Hello All,

I am picking .CSV files from a folder through Power Query (Name: "Traslados_Envia_csv"). Recently the owner of the .CSV Files has added a new Column ("Column 9") with some new data. Once I tried to refresh the Query it shows the following error "[DataFormat.Error] There were more columns in the result than expected" (This error disappear if I delete this new Column manually. However these data has to be keep it)

Error

When I go to the query I have tried to modify the Code in the "Advance editor" by trying to add the Column manually but the problem is not solved. See below:

Original Line

#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7", "Custom.Column8"}),

Modification

#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7", "Custom.Column8", "Custom.Column9"}),

It seems that they way that I build the models does not work with dynamic columns ?

 

Please find Enclosed:

File where the query is done: "BOLSA_VS_GUIAS_ENVIA _V2" (The query is in the Tab "Traslados")

Raw File: "34969_ORIGINAL_$1.169.797" (The file with the new Column that causes the problem)

Raw File "32295_01032021_$1.139.200" (Sample file with the old layout, wich with the Query works)

 

Additional notes:

  • The File has two additional Querys more. The A That pulls .xls file from the same folder where Query B (causing the problems) pulls the . CSV files and a C Query that joins Query A and B.  
  • If you want to replicate the exercise please check that the query is looking for the location of the folder in the Tab "DyRefs" cell "K4"

 

I have done some research but I have not found a proper solution.

Thanks in advance.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Sebastian Fuentes

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 8, 2021
sp_UserOfflineSmall Offline
2
April 8, 2021 - 6:57 am
sp_Permalink sp_Print

Sorry I am new in the forum and forget to upload the files 🙂

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
3
April 8, 2021 - 12:05 pm
sp_Permalink sp_Print

Hi Sebastien,

I'm not clear on whether you are now trying to merge files where some have 8 columns and some have 9?

Looking at your queries it looks like you are adding a Custom Column and then expanding that column to get the file contents.  You should be using the Content column and using the Combine Files icon - see attached image

I've duplicated your queries but as I don't have any XLSX files I can't load or combine them with the CSV. I've moved your queries into a group called Original Queries with Errors and my queries and in the Other Queries group - see attached file.

In the query that loads the CSV I have used the Combine Files icon, then removed all unnecessary columns.  I can't do the transforms as I don't know what you need to do.

Please read this post to see how to Combine Files from a folder

Power Query Get Files from a Folder • My Online Training Hub

and if you are still having issues post back here.

Regards

Phil

sp_PlupAttachments Attachments
  • sp_PlupImage sf.png (9 KB)
sp_AnswersTopicAnswer
Answers Post
Avatar
Sebastian Fuentes

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 8, 2021
sp_UserOfflineSmall Offline
4
April 9, 2021 - 3:00 am
sp_Permalink sp_Print

Hello Philip,

Thanks for your support. It works !!! with the Content Column.

Best

Sebastian

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
April 9, 2021 - 5:09 pm
sp_Permalink sp_Print

OK, glad to help.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Riny van Eekelen, Albert Hislop, Kumud Patel, Bright Asamoah
Guest(s) 7
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Forum Stats:
Groups: 3
Forums: 24
Topics: 6360
Posts: 27812

 

Member Stats:
Guest Posters: 49
Members: 32340
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.