how to compare same data from an undefined number (evolution of the list) of excel files ?

[Anonymous]

Hello,
I have a folder with excel files having the same structure (just 1 sheet with rows and column names).
Each file is an extract of people having an option on their phone plan to enable using more data than foreseen in the original phone plan. This option is supposed to disappear over time for each person. If a person has this option enabled, then there is a line in the file with its name, its phone number, the date where the option was enabled for him and the amount of data used outside the plan thanks to this option (amount so far since the begining of the month).
A new file is added in the folder every few days (it has the same name with only a date (the day the information has been extracted) that differs in it) to this folder.
I am asked to create a Power BI dashboard/report to present the evolution of the number of people still having this option (knowing that new persons can be added over time but little by little people must have this option removed for them) to make sure we are going in the right direction to remove the option for everyone.
I have combined and loaded all the excel files in the folder, and I have added 1 column that represents the date of the file in order to differentiate similar lines if a persone still has the option between 2 extracts.
But from there I am a bit lost about how to represent relevant reports. At that point, in my model, I have then only one table, should I change that or can I already make relevant reports like that ? (I have already made several Power BI reports but mainly by using the transformation features and presenting the result in a table, not by handling the model, so I don't know what I can get from thinking of the model). How do you think this should be represented and how to do please ?
Or maybe you can point me to similar business cases already handled by Power BI because I couldn't find that.
Thank you for your help

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, you have a folder that contains many Excel sheets with the same data structure, and every few days a new file with the same data structure will be added to the folder, in this case, the recommendation we Power BI can give is to use to create a SharePoint site and place these folders, and then use PBI desktop's SharePoint folder connector to connect this folder. If you click "Combine&Load" when connecting, then PBI will automatically generate a function to merge and load these files with the same table structure, and then when the data is refreshed, if a new file enters the folder, it can also merge and load the new file into the PBI's data model through the function, please refer to this official document:

Power Query SharePoint folder connector - Power Query | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hello,

Thank you for taking time on my issue but I have already done that combine part (using folder instead of SharePoint folder at the moment).

I then did the transformation needed to keep only the meaningful columns and added one column that is specific to each file, kind of a file ID but made of the date that is included in the file title.

My concern is what to do next (keep the data model as the single table or create some kind of dimension tables around it ?) in order to present as many information as possible regarding the evoliution of the content of the file. What would be your guess or principles or links about that please ?

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, you want to describe the changes in different periods based on different documents? For your needs, you can use time as your file name, and then the new column takes time as the dimension, and then you can use the time column to further analyze your data.

If that's not your purpose, you can describe your specific needs and your results in detail.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly