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:
When I connect to PBI, it excludes the last column for some reason ("Realizado" column):
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.
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.
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.
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.
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).
