How to work with monthly Datasets?

[ornicus]

Hello,

I am exporting monthly the Outlook user list of my compagny in order to make some KPIs (like the number of people per department, the locations of the employees,...).

I did build a Dashboard that I update monthly by editing my query to change the source of the dataset.

Now I wish to compare my reports but instead of duplicating them every time, would it be possible on the click of a button in the report to change the source to the month Dataset I want?

I imagine some buttons with months written on it and once I click on 'JUN' it would change the source to the 'Dataset_June_2017.xlsx' for example.

Also, would it be possible to make a comparison between 2 dataset? For example to be able to highlight witch department did grown the most since the previous month. How schould I proceed?

Thanks

[v-jiascu-msft]

Hi @ornicus,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[ornicus]

So the Folder trick was what I needed. I added then a slicer linked it to 'Source.Name' and now I can navigate between my monthly extracts very easily.

Thanks for the support guys!

[v-jiascu-msft · Accepted solution]

Hi @ornicus,

Another idea I would suggest using a new type of data source called Folder. Every time you add a file into it, then refresh Power BI you will get all the data in one dataset from many excel files. Finally it's easy to compare the data of different month due to they are in one table (or called dataset).

1. "Get Data" connects to folder.

1) Click OK, you will get two records. 

2) If you use "Combine files" (2), you will get one query with all the files. I guess they all have the same structure.

3) If you right click 3, then select "Add as new query", you will get new query (4). Single file of course.

2. You can have all the records from many files in one table. 

3. Add a new file into the folder, refresh it. No other steps needed. As you can see the records from "June" have been read in, while the preview of Query Editor still doesn't have them because I don't refresh the query view.

Best Regards!

Dale

[ornicus]

Thanks too for your answer, I think I will use this.

That way, I will be able to easily compare data from one month to another and I just need to build abutton that will apply filter on the month of the extract I want to display.

I just hope that the amount of data will not be too huge (100k row each month and 10 usefull columns).

[Anonymous]

You may be able to do this with parameters. Check out this link:

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

Essentially you build out part of filename, add a new part and select parameter, and then build out the rest of the filename.

This is done in the datasource settings for your Excel file connection.

It's hidden here, but you can see a File path preview that shows what the file connection will look like.

[ornicus]

Thanks for your answer, I will look into this feature.