Combining files from folder deletes column data of 2nd file

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
212
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"
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
212
Column header had 1 character difference and it was ruining everything, got it figured out. Do not know how to delete the thread
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
Just say SOLVED and that's all :)

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

Watch MrExcel Video

Forum statistics

Threads
1,101,815
Messages
5,483,075
Members
407,377
Latest member
JennaWashburn

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top