M Code for dynamic path & file name

[Dampfnudel]

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

My current 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.

 

[asjones987]

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

 

[Dampfnudel]

Thank you for your suggestion!

    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!