none
Insert " " around Access database location RRS feed

  • Question

  • Hi,

    I'm having trouble with the following:

    let
        Source = Folder.Files("\\pdc_N\New Data\Stock_History\2018"),
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
        #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Location", each [Folder Path] & [Name]),
        #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Location"})
    in
        #"Removed Other Columns1"

    I want " " around location and name, i.e. "\\pdc_N\New Data\Stock_History\2018\Feb18 Stockm.mdb" as opposed to  \\pdc_N\New Data\Stock_History\2018\Feb18 Stockm.mdb resulting from the code above.

    Any help appreciated.

    Jonathon

    Saturday, March 31, 2018 4:36 PM

Answers

  • I don't think you need the Folder query. If the naming convention of every file will remain the same, then all you need to do is change the part that has the MMMYY (example: Mar18). You could have a dropdown with the values that you need (Mar18, Jun18, Jul18, Dec18, Jan19) and then concatenate that to the filepath used in the File.Contents function (\\pdc_N\New Data\Stock_History\Jun18 Stockm.mdb) and that should simplify the experience.
    Sunday, April 1, 2018 3:24 PM

All replies

  • Hey Jonathon,

    Your code already has the quotes needed in the Folder.Files line. Are you referring to something or some other place where you need the quotes? if you need them to show for a specific value then you'd need to use 4 quotes like this """"abc""""

    Here's an example:

    let
        Source = #table({"ColumnA","ColumnB"}, {{"My name is ", "ABC"}, {"My name is ", "XYZ"}}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnB", type text}, {"ColumnA", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each """" &[ColumnA] & [ColumnB] &"""", type text)
    in
        #"Added Custom"

    EDIT: Furthermore, if you're trying to connect to those access databases, then I'd recommend using the binary inside the [Content] column against the Access Data Source function instead of using a filepath. Creating a filepath adds an unnecessary overhead to the query which might make your query run really slow.
    • Edited by Miguel Escobar Saturday, March 31, 2018 5:34 PM adding more info
    Saturday, March 31, 2018 5:32 PM
  • Thanks Miguel - the four quotations did what I was asking for above, however I may be getting myself confused as to what I'm try to do with the results of that query.

    Long story short, I wanted to create a drop down list in Excel (from the results of the query referred to in my original post), and use a named cell from the drop down list to populate the bold and underlined part of the code below:

    let
        Source = Access.Database(File.Contents("\\pdc_N\New Data\Stock_History\Feb18 Stockm.mdb"), [CreateNavigationProperties=true]),
        _scheme_stockm_Feb = Source{[Schema="",Item="scheme_stockm_Feb"]}[Data],

    The Feb18_Stockm.mdb will turn into Mar18_Stockm.mdb and so on and so on as we progress through the year - in essence all I'm trying to do is find a better way update the data source rather than having to go into the Query Editor and manually change every month.

    Hope this makes sense.

    Jonathon


     
    Saturday, March 31, 2018 6:24 PM
  • I don't think you need the Folder query. If the naming convention of every file will remain the same, then all you need to do is change the part that has the MMMYY (example: Mar18). You could have a dropdown with the values that you need (Mar18, Jun18, Jul18, Dec18, Jan19) and then concatenate that to the filepath used in the File.Contents function (\\pdc_N\New Data\Stock_History\Jun18 Stockm.mdb) and that should simplify the experience.
    Sunday, April 1, 2018 3:24 PM