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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nok
Helper I
Helper I

Combine excel files not seeing the last column of each file

Hello,

When I try to connect and combine all files in my Sharepoint folder, the last column of this xslx files doesn't show up for me in Power Query and I really don't know why.
This is the xlsx I'm trying to connect:
erro01.PNG


When I connect to PBI, it excludes the last column for some reason ("Realizado" column):
erro02.PNG


Edit: I saw that there is a way to make Power BI read the sheet instead of the metadata content using [DelayTypes = true, InferSheetDimensions = true] in the Excel.Workbook line. When I connect PBI directly to one single xlsx file and use these parameters in the Excel.Workbook line, all columns appear perfectly for me.
But as I'm combining several excel files in the same folder and not connecting to just one single excel, I don't know how to use [DelayTypes = true, InferSheetDimensions = true] without giving errors.

Anyone know how to do this?

1 ACCEPTED SOLUTION

Thanks for the new information. You didn't say you were using file combine.  I suggest you take a look at this article https://exceleratorbi.com.au/understanding-power-query-combine/

It is possible that the file combine has not worked as needed. Using the above article, you should be able to fix this by first editing the sample query and then editing the combine query. In the sample query, work your way backwards deleting the steps from the bottom up until you just have the excel sheet, then import it again.  

take a backup of your file first. There's no undo available. 

It's also worth mentioning that it is the first file in the folder (based on natural sort order) that is used as the sample file. Even if that file has changed since you created the load, it can impact the way the files are loaded in the future (it depends). If you sort out the sample query, you should be able to resolve it. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

5 REPLIES 5

Thanks for the new information. You didn't say you were using file combine.  I suggest you take a look at this article https://exceleratorbi.com.au/understanding-power-query-combine/

It is possible that the file combine has not worked as needed. Using the above article, you should be able to fix this by first editing the sample query and then editing the combine query. In the sample query, work your way backwards deleting the steps from the bottom up until you just have the excel sheet, then import it again.  

take a backup of your file first. There's no undo available. 

It's also worth mentioning that it is the first file in the folder (based on natural sort order) that is used as the sample file. Even if that file has changed since you created the load, it can impact the way the files are loaded in the future (it depends). If you sort out the sample query, you should be able to resolve it. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

try refreshing the preview. PQ caches a preview of the file. If the file changes, the cache needs to be updated. If that doesn't work, you could try connecting to the file again. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hello @MattAllington  Thanks for the response!

When I go to the xlsx and rename the column to the same name "Realizado" and update it in Power Query, PBI shows this last column.

But I can't do this because this xlsx will be extracted automatically and I need Power Query to be able to see this last column without me having to rename it manually.

What happened when you tried the suggestions I made earlier?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Even updating the preview or connecting to the file again, it doesn't show the last column. ☹️


I discovered that when this happens it's usually because the Excel metadata is bad, so you need to tell PQ to read the entire spreadsheet instead of the metadata, but I only know how to do this in a direct connection to a single Excel file and not in a connection that combines several Excel files within a folder (which is mine).

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

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