Power BI Advanced Editor to Filter Column based on the PBI file name

[MarkCBB]

Hi there,

I would like to know if it is possible and how, to add some "code" to the query in order to filler the column that contains the file names by using the name of the PBI name. Example, I am importing files from a folder, each of the files have names i.e. Cat.xls, Dog.xls, Mouse.xls etc.. I am creating 3 seperate PBIX files Called "Cat.pbix" "Dog.pbix" etc. I would like to filter the data by the name of the PBIX name, Possible? how?

Please help

[Cramos]

Hi!

I have the same problem, i have 80 PBI files with the name of each deparment for each file. The deparments cant see other deparment's info, thats why i create differents files.

The point is to save the time in filter each file as well as to avoid a human mistake in the filter being made manually. If each file will filter by the name of the file it would be perfect.

Thanks

[tjd]

If all of your .xls files are in the same folder, e.g., cat.xls, dog.xls, etc. then, as you create each .pbix file, you could filter your folder files by the pbix file name you are working on.  For example, you could set the cat.pbix file to filter the folder for only the cat.xls files in the folder.  When doing a "Get data from folder", one of the first things you see instead of transactions is a list of files in the folder and their properties.  You can then filter a particular column and/or create new columns to use for filtering to select the list of folders that apply to the particular pbix you are building.  For a more detailed explanation, I would refer you to the book "M Is For (Data) Monkey", Chapter 4.

[Greg_Deckler]

Your best bet would be to use the Advanced Editor to write "M" code but not sure you have access to the name of the PBIX file from M. You could use an Enter Data query to enter the name of the PBIX file and then potentially do something there.

I am missing the reason for this requirement, is it because you have more than 3 files involved here? I don't see the time savings versus just configuring each file. Then again, why not just import all of the data into a single file as separate tables? I feel like I am missing some requirements here.

[Anonymous]

Hello @MarkCBB

I didn't wee M Langage function in Power Query, which does what you want (i.e. getting the pbix filename). 

Here is something which can be a workaround, you build you business model just as you want for the analysis you want. 

You add a table with the enter data feature : This create an artificial table source with data you can enter. In the column you enter the filter you want to use (i.e "cat", "dog", ...).

You use the merge query filter to use the artificial table as a query filter (don't forget to specify that it will be a inner join). Choose the field corresponding to the merging condition, and the data should be filter with Power Query.

Hope it will answer your needs

Sebastien