Dear All,
I have never come across this kind of issue before. I really need your help.
I have a file which is connected to a folder via Power query. I have applied all the transformations and combining monthly files to give me Year to date file, Each month I save monthly Transactions files in that folder.
Everything was working fine but this month when I saved the latest month transactions, power query could not find a specific column. I checked the original file, everything is correct and all the columns are there.
To verify, I connected a fresh excel file with that folder and it was strange that there are 38 columns in the original file but power query is only importing 37, the last column is missing. I am not sure why this is happening.
The files am importing all are in XLSX format.
Any help would be greatly appreciated.
Thanks.
Without seeing the query we can only guess, so most likely cause I think would be that the column name is not exactly the same as specified in the query (same case, no trailing spaces etc).
Hi,
I have connected to the file using a fresh connection and without applying any transformations.
Even when you connect to a folder, it shows you the preview - at that time it does not show the last column.
Still the Power Query is not importing all of the columns, it is missing the last column.
below is the code:
let
Source = Folder.Files("C:UsersUser1DownloadsReport"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}})
in
#"Changed Type"
----------------
Your help would be really appreciated.
It may be helpful if you attach the file here so that we can test the the query.
I suspect the issue is in your transform file function.
I assume the problem has been resolved since there is no activity for a while.
However, I'll add my two cents. I had a similar issue when I imported a file. The last column did not import. It turned out that the last column was not part of the table. I just adjusted the table width and it worked.
Cedric