How do I Use Text from Cell Drill Down as Data Source and File location.

[MartinDUK]

I have files in a Network Folder and have written a query to get the list of files from a Folder.

Filtered the File name for the relevent file.

Filtered the Latest file.

Drilled down in the Cell to get a Text Value for the File location.

How do I use this value as a Data Source?

Regards,

Martin.

[AlienSx]

Hi, @MartinDUK use Csv.Document(FilePath) Csv.Document(File.Contents(FilePath)). Add optional parameters if you need to (delimiter, number of columns etc.). Simply read (get data from) that CSV file (PQ creates all parameters for you) and replace "...path..." value by your FilePath query name. 

[MartinDUK]

I'm sorry I don't understand that, im quite new to Power BI.

All I want to do is select a file from a Network Drive, the File name changes every day by adding the Date at the end of the File. So I did the query to filter the file name and location but do not have the knowledge to use this txt to get the Excel file from the Network Location.

[AlienSx]

create blank query, open it with Advanced Editor and literally write the following 

let 
  // Source = Csv.Document(FilePath) - wrong!
   Source = Csv.Document(File.Contents(FilePath))
in Source

what do you get?  

[MartinDUK]

I get the file and File Path, which I've already got.

How do i use this to import the Excel file displayed?

[AlienSx]

I am sorry, my fault. Try this please

let 
  Source = Csv.Document(File.Contents(FilePath))
in Source

[eduardosa]

MartinDUK,

Set data source as being a Folder, and from this step do all the transformations you require to select the latest file.

Regards,

Eduardo

[MartinDUK]

Hello Eduardosa,  I have already done that as stated in my origional question  🙂

[eduardosa]

Hi MartinDUK,

Then can you please show all the steps you're doing?

Something like this.

If I understoud correctly you want the content of the latest file.

For this do:

• Set Source data a Folder,
• Sort by create date,
• Add index, start with 1,
• filter index by 1
• expand file content.

Give it a try.

[MartinDUK]

I do not know what the following means ...

• Add index, start with 1,
• filter index by 1
• expand file content.

I have got the file and File Path, how do i use this to import the Excel file displayed?

[eduardosa]

MartinDUK let me breakdown the steps for you.

So starting with accessing data on a Folder:

• Sort by creation date

• Add index

• Filter by 1 ( to get latest file)

• Get file content

Regards

[MartinDUK]

I found easy to understand and follow instructions here....

Thankyou to those who offered advice, much appreciated 🙂

https://www.youtube.com/watch?v=pHDRKNjuwmU&ab_channel=BI-LingualAnalytics

[MartinDUK]

Unfortunately this didn't work  😞  

[Einomi]

Try

Let

Source = FilePath,

Yoursteps = ...

in

Yoursteps

??

[MartinDUK]

I found easy to understand and follow instructions here....

Thankyou to those who offered advice, much appreciated 🙂

https://www.youtube.com/watch?v=pHDRKNjuwmU&ab_channel=BI-LingualAnalytics