Forum Discussion

ornicus's avatar
ornicus
Frequent Visitor
9 years ago
Solved

How to work with monthly Datasets?

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 Das...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

     

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

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's avatar
    ornicus
    Frequent Visitor
    9 years ago

    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).