The spreadsheet was opened and re-saved in Excel 2007 format Workbook (filename.xlsx) creating a version conflict.

Solution

If the file was saved in the format "Excel 93-2003 Workbook (*.xls)", then the ODBC can correctly read the file as a table.

If the file was saved in the new XML format using "Excel Workbook" in Excel 2007, the external table is actually in XML and not the expected format which is a native Excel table object. Although Excel 2007 successfully opens these files when double-clicked, just opening the file in Excel is not sufficient to determine if the file can be read using ODBC.

You need to go to your ODBC administrator and add a new entry for the new format of Excel files. When you click on Add, there should be an entry for a Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) . You may also need to delete your old Excel entry.

I am trying to combine files that are in one of our shared folders ( network drive not sharepoint) so i go into get data from folder navigate to the root folder in question and it transform dasta. this all seems to go through fine. i then selected a random file that i knew no one was using at the time (as the first file in the folder was in use by someone else) and asked it to combine for the data in within sheet4 (name not important) this seemed to work other than this sheet having a few rows with merged headers before the main header row so I needed to change what the promoted header row was.

Itried doing remove top rows then promoting from within the merged query but that kept coming to an error as it was only removing the top rows from the first file not all 99 files in the folder. after a bit of gogling I realised I needed to do the remove rows step within the sample query transform query rather than in the output as i'm used to (don't normally do complex PQ)

So I went back to this query deleted the stages it had added after navigation (the automatic promotion of headings. then I removed the top rows that I knew were just where my merged cell headings were and then hit use first row as headers. However the file now generates the following error only after the promotion (was in if i don't promote the heading).

DataFormat.Error: External table is not in the expected format. the details just provide the file name of the file i used for my sample file. i can't think of anything wrong with the file as it is built in excel and is rather simple design other than having merged cells which make it not a simple merge task.

Any idea why this error would come up only if i use the promote headers step after deleting the top rows which are in the format of the table below (example given to show why they aren't useful headings)

main group 1 main group 2 sub group 1 sub group 2 sub group1 sub group 2

I am not sure how i can workaround this other than copying and pasting all the data into a single sheet which is unfeasible and removes the purpose of the query, or delete the column i would normally use as the row headers from the sample file then rename all the columns to what they should be called in a subsequent step. If anyone has a better solution I'm open to ideas.

Monthly Community User Group Update | April 2024

The monthly Community User Group Update is your resource for discovering User Group meetings and events happening around the world (and virtually), welcoming new User Groups to our Community, and more! Our amazing Community User Groups are an important part of the Power Platform Community, with more than 700 Community User Groups worldwide, we know they're a great way to engage personally, while giving our members a place to learn and grow together. This month, we welcome 3 new User Groups in India, Wales, and Germany, and feature 8 User Group Events across Power Platform and Dynamics 365. Find out more below. New Power Platform User Groups Power Platform Innovators (India) About: Our aim is to foster a collaborative environment where we can share upcoming Power Platform events, best practices, and valuable content related to Power Platform. Whether you’re a seasoned expert or a newcomer looking to learn, this group is for you. Let’s empower each other to achieve more with Power Platform. Join us in shaping the future of digital transformation! Power Platform User Group (Wales) About: A Power Platform User Group in Wales (predominantly based in Cardiff but will look to hold sessions around Wales) to establish a community to share learnings and experience in all parts of the platform. Power Platform User Group (Hannover) About: This group is for anyone who works with the services of Microsoft Power Platform or wants to learn more about it and no-code/low-code. And, of course, Microsoft Copilot application in the Power Platform. New Dynamics365 User Groups Ellucian CRM Recruit UK (United Kingdom) About: A group for United Kingdom universities using Ellucian CRM Recruit to manage their admissions process, to share good practice and resolve issues. Business Central Mexico (Mexico City) About: A place to find documentation, learning resources, and events focused on user needs in Mexico. We meet to discuss and answer questions about the current features in the standard localization that Microsoft provides, and what you only find in third-party locations. In addition, we focus on what's planned for new standard versions, recent legislation requirements, and more. Let's work together to drive request votes for Microsoft for features that aren't currently found—but are indispensable. Dynamics 365 F&O User Group (Dublin) About: The Dynamics 365 F&O User Group - Ireland Chapter meets up in person at least twice yearly in One Microsoft Place Dublin for users to have the opportunity to have conversations on mutual topics, find out what’s new and on the Dynamics 365 FinOps Product Roadmap, get insights from customer and partner experiences, and access to Microsoft subject matter expertise. Upcoming Power Platform Events PAK Time (Power Apps Kwentuhan) 2024