Results 1 to 3 of 3

Thread: M Code for dynamic path & file name

  1. #1
    Neophyte Dampfnudel's Avatar
    Join Date
    Mar 2021
    Posts
    2
    Articles
    0
    Excel Version
    Office 365 E3

    Question M Code for dynamic path & file name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I am new here!
    I need some help to write a Power Query Code (M)

    My current code:

    Code:
    let
        Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
        Importpath = Excel.CurrentWorkbook(){[Name="Importpath"]}[Content]{0}[Column1],
        Sheet = Excel.CurrentWorkbook(){[Name="Sheet 2.1"]}[Content]{0}[Column1],
        Quelle = Excel.Workbook(File.Contents(Filepath & Importpath &"13.11.2020_Umlagenausfall_FC20_BUD21_V06_ak.xlsx"), null, true),
        #"Choose Sheet" = Quelle{[Item=Sheet,Kind="Sheet"]}[Data],
    
    .......
    
    in
        #"Neu angeordnete Spalten"
    "Filepath" and "Importpath" are defined ranges in the Workbook where excel defines the path the workbook is located and what sub path the data is located.
    "Sheet 2.1" is a defined range in the Workbook where excel defines what sheet that query needs.

    13.11.2020_Umlagenausfall_FC20_BUD21_V06_ak.xlsx is a different name every "reporting cycle"

    What I need is a M Code that allows to grab whater xlsx file is located in that "Importpath" filepath.

    I do know that Power query allows to grab filepaths instead of files. But when I do that, excel creates dozens of "example/help queries" which I do not want. I want a clear M code.

  2. #2
    Seeker asjones987's Avatar
    Join Date
    Feb 2020
    Posts
    10
    Articles
    0
    Excel Version
    2019
    i was a little confused by part of your question. I have been looking for something similar abut not found much. From what i have learned Power Query can' tell you the file name or current path for that file. Instead what you do is use Excel formulas to get the file path and/or file name etc. Then have Power query reference that cell in Excel (via named range or table then do a drill drown so the value can be used as a parameter.


    I use this formula for current file path
    =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

    Then I use this for the file name
    =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

    Then just for completeness i use this for the sheet name
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


    Then make the items a table or name the cell. Then in power query use something like
    = Excel.CurrentWorkbook(){[Name="fn_File_Name"]}[Content]
    then do a drill down to get the value.

    I am playing around with is a parameter table. The table has two columns
    ITEM Parameter
    File Name <something>
    File Path C:\some path
    other items other parameter
    other items other parameter

    I name the table parameter and import it in. Then I reference the table with a new query. Filter down to the item i want. then do a drill down on the parameter item and name it. In some cases I will use the items to help me with other parameters for the querey


    hope this helps

    Alan

  3. #3
    Neophyte Dampfnudel's Avatar
    Join Date
    Mar 2021
    Posts
    2
    Articles
    0
    Excel Version
    Office 365 E3
    Thank you for your suggestion!

    Code:
      
        Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
        Subpath = Excel.CurrentWorkbook(){[Name="path_2.1"]}[Content]{0}[Column2],
        Mappe = Excel.CurrentWorkbook(){[Name="path_2.1"]}[Content]{0}[Column3],
        Path = Folder.Files(Filepath&Subpath),
        File = Path [Name]{0},
        Quelle = Excel.Workbook(File.Contents(Filepath&Subpath&File), null, true),
    This is my code now and it works!

Tags for this Thread

Posting Permissions

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