none
Importing Multiple Files from Folder RRS feed

  • Question

  • Hello all,

    I'm currently working on linking two forms within a folder (Form A and Form B, both of which have, for the most part, matching IDs and ~50 other demographic variables). I would then create a report based on only IDs that match between the two forms.

    I am importing the two forms by pointing power query to the folder within which Form A and B live

    The issue I'm running into is, every week, there is an update that will add new IDs to both forms.

    So one week would be 01.01.2018 (Form A) and 01.01.2018 (Form B); the next week will be 01.08.2018 (Form A) and 01.08.2018 (Form B)

    I'm just running into an issue when move the 01.01.2018 files into an Archive folder and moving the new files into the folder from which power query is pulling. However, when I refresh, the error "The key did not match any rows in the table" will appear. I know this is because the new files now have new names, but I am wondering whether there is a way to have power query pull from that folder with new file names, as I would just like to keep track of the most recent exports I am working with.

    Darko

    Thursday, October 25, 2018 1:39 PM

Answers

  • I'd probably put the form A on one folder and the form B on another one. Then create a query for each folder and name them however you'd like. That would be the most simple way to tackle this situation. Another way would be to rely on the name of the actual file, for example, if the name of the file contains the word "A" or some sort of unique name for that specific form.
    Thursday, November 1, 2018 11:52 PM

All replies

  • Could you share the M code of your "Sample File" query?
    Thursday, October 25, 2018 1:53 PM
  • Certainly!

    So this is the code that imports the data from the folder:

    let
        Source = Folder.Files("G:\R&D\_PUBLIC SHARED\PowerTracking\TOMM\Mock Raw Data"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Mock Dx Form Data TOMM 9 13 18 -   Copy.csv")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
        Content = #"Removed Other Columns"{0}[Content],
        #"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=916, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SiteId", type text}})
    in
        #"Changed Type"

    But I'll use that output generated from the initial import to create the the two separate forms as queries.

    So I would just right click within the "Content" column and add each form as a new query. 


    I'm thinking there's definitely a way to do this more easily, but can't quite make the connection.

    Thursday, October 25, 2018 3:38 PM
  • Hi Shivam

    Here is the screenshot:

    Power Query ScreenShot
    • Edited by Darko Giacomini Thursday, October 25, 2018 5:15 PM Editing screenshot link
    Thursday, October 25, 2018 5:14 PM
  • Hi Darko. Try indexing into the list of files using a number rather than a file name. For example:

    #"Changed Type"{0}

    As long as the A and B files are always in a predictable order (based on their names), this should work.

    Ehren

    Thursday, November 1, 2018 10:50 PM
    Owner
  • I'd probably put the form A on one folder and the form B on another one. Then create a query for each folder and name them however you'd like. That would be the most simple way to tackle this situation. Another way would be to rely on the name of the actual file, for example, if the name of the file contains the word "A" or some sort of unique name for that specific form.
    Thursday, November 1, 2018 11:52 PM