Combining files from folder deletes column data of 2nd file
Results 1 to 4 of 4

Thread: Combining files from folder deletes column data of 2nd file
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combining files from folder deletes column data of 2nd file

    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"

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining files from folder deletes column data of 2nd file

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

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Combining files from folder deletes column data of 2nd file

    Just say SOLVED and that's all

    btw. for any code use CODE tags, like: [CODE]your code here[/CODE]
    Last edited by sandy666; Jul 8th, 2019 at 09:37 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    Board Regular
    Join Date
    Mar 2016
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining files from folder deletes column data of 2nd file

    Solved

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •