none
How can I pass a folder name into a query for importing? RRS feed

  • Question

  • I have 3 folders: Red, Green, and Blue

    How would I set up a query to be able to select "Blue" in Excel and import from this filepath?

    C:\Users\ME\Desktop\Blue

    Wednesday, March 28, 2018 10:24 PM

Answers

  • Then the formula should work. That' the full code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"FolderName", type text}}),
        SelectedColour = Text.From(#"Changed Type"{0}[FolderName]),
        Custom1 = Folder.Files("C:\Users\ME\Desktop\"& SelectedColour)
    in
        Custom1 

    and the sample file: https://1drv.ms/x/s!Av_aAl3fXRbehbFfnC5BYj4NqWbSxA


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!




    Thursday, March 29, 2018 8:55 AM
    Moderator

All replies

  • Hi Oz,

    not sure if this is what you were asking: "C:\Users\ME\Desktop\" & SelectedColour

    Where SelectedColour is either the step where you've retrieved your parameter or the query name (if you prefer to create a separate query for that).


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Thursday, March 29, 2018 5:45 AM
    Moderator
  • I would like to have a dropdown list on a sheet, select "Blue", hit Refresh and import everything from the Blue folder. Then, at another time, select "Red", Refresh and have the Red folder content come in (replacing the Blue content).

    Thursday, March 29, 2018 7:10 AM
  • @SunData

    Excel Sheet1 has dropdown list (with Blue, Red, Green) in ie A1. Name A1 (at the Workbook level) as ie. qryColor . In another sheet setup a table as below and name it ie. tblColorPath:

    let
        tblColorPath = Excel.CurrentWorkbook(){[Name="tblColorPath"]}[Content],
        qryColor = Excel.CurrentWorkbook(){[Name="qryColor"]}[Content]{0}[Column1],
        FolderPath = Table.SelectRows(tblColorPath, each [Color] = qryColor)[Path]{0}
    in
        FolderPath

    Thursday, March 29, 2018 8:52 AM
  • Then the formula should work. That' the full code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"FolderName", type text}}),
        SelectedColour = Text.From(#"Changed Type"{0}[FolderName]),
        Custom1 = Folder.Files("C:\Users\ME\Desktop\"& SelectedColour)
    in
        Custom1 

    and the sample file: https://1drv.ms/x/s!Av_aAl3fXRbehbFfnC5BYj4NqWbSxA


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!




    Thursday, March 29, 2018 8:55 AM
    Moderator
  • Imke THANK YOU THANK YOU THANK YOU!!!

    This works. I'm studying the code to see how it works, but it's working. 

    Thursday, March 29, 2018 9:19 PM