Folder as parameter

[Anonymous]

Hi,

I have a requirement where i want to read data from a folder location.

This folder has 10 excel files and i want to show these 10 as a filter parameter for a user to choose and my visuals should be filtered based on the data from that excel sheet.

I am able to pick up latest file by default (Keep Top rows 1) but is there a way to give user to filter which file they want to analyse from that folder.

FYI: all files are of same extension, just weekly some data is added/removed from the file.

Thanks in advance.

Namish

[nsr299]

Newbie here...

Is it possible to create a parameter that can swith between a folder and Sharepoint folder as a source?

Thanks!

[Greg_Deckler]

@Anonymous - So, are you using a Folder query or are you grabbing them all individually? When you use a Folder query, there is a step where the name of the file is included. I think it gets selected out but you could modify the queries to keep it. Not sure if this is what you are going for or something else. It's not super clear.

[Anonymous]

Thanks @Greg_Deckler., sorry if i am not clear

I am trying to read data from a folder as Data source. Assume folder contains 10 files If i read the latest modified file (Top 1 row) this works perfect.

My question was if i need to show the user a list of file names in this folder (10 names), so they can choose which file they want to analyse?

I hope this will be clear? Is there any clean way to do the user input on folder?

Cheers

[Greg_Deckler]

@ImkeF @edhans , how do you preserve the Source.Name field in a Folder query so that it shows up as a column in the final table? Thanks.

[edhans]

@Anonymous - to preserve any columns after you've done a combine operation, go back to the automatically generated Remove Other Columns1 step, hit the gear icon, and check what you want to keep. I think the August 2020 version of Power BI Desktop does this automatically for you (I just tested) but I know this has not always been the case, and it may depend on the types of files (text, Excel) and where you are combining from (OneDrive, local folder, SharePoint Library, DataLake, etc.)

You can see that Folder Path is  there for selection as well if you want it.

[Greg_Deckler]

@edhans - Thanks, for some reason I could not track that down!! @Anonymous does this get you where you need to be?

[amitchandak]

@Anonymous , Not sure I got it . but you have Folder.Files, that will return you table of files

https://docs.microsoft.com/en-us/powerquery-m/folder-files

also refer : https://radacad.com/power-bi-get-data-from-multiple-files-in-a-folder-on-onedrive-for-business-no-gateway-needed

https://medium.com/@wkrzywiec/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711