Combining files from folder deletes column data of 2nd file

[Craigc3814]

I have never seen this before. I have 2 files in a network folder and they both show available fund remaining for every project that my company has. One is from February, and I formatted it about a month ago so that as new available fund reports come out I can just drop them in there, PQ would grab the most recent report and format the exact same way.

When I am dropping my second file into the folder it is not bringing in the second files first column data. It has a place holder for the data but it only returns null. If I filter to only the second file it still does the exact same thing. If I go to a new excel file and try to recreate the query from scratch it still does the same thing.

BUT if I create a new excel file and instead of loading the data from Folder I select load data from file and I click on the second file it brings in absolutely everything that is there. I have spent several hours now looking for a format issue that would cause the error.

Has anyone experienced a similar issue? The files contain financial information from a government entity so I cannot share them, I am really just looking for ideas as to what could be causing the error before I say forget it and just make it a file change every single month


let
Source = Folder.Files("C:\Users\Joe Shmo\Desktop\Actuals"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Actuals", each #"Transform File from Actuals"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Actuals"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Actuals", Table.ColumnNames(#"Transform File from Actuals"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"FY19 Capital Budget / Available Funds", type text}, {"Column2", type text}, {"ALL COLUMNS WITHOUT FORCE ACCOUNT", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Available Funds Feb 28 2019.xlsx", type text}, {"Budget ID", type text}, {"Project Name", type text}, {"Contractual Commitments", Int64.Type}, {"Prior Year Actuals", type number}, {"FY19 Budget", type number}, {"Lifetime Actuals", type number}, {"Current Year Actuals ", type number}, {"Available Funds", type number}, {"Column10", type any}, {"Column11", type any}})
in
#"Changed Type1"

 

[Craigc3814]

Column header had 1 character difference and it was ruining everything, got it figured out. Do not know how to delete the thread

 

[sandy666]

Just say SOLVED and that's all

btw. for any code use CODE tags, like: [CODE]your code here[/CODE]

 

[Craigc3814]

Solved