Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Big Project Need Advise/Help

So recently i have been learning and using Power BI for my company. However i have been given a rather big task as my first and i could use some help with some little bits along the way.

 

So the current process is all manual.

10 files arrive each in their own folder, within that folder there is a single .zip file and inside that is a file called EmailProtectionSomething.csv

 

We would then need to unzip each individual file and open them.

Then we need to do a VLOOKUP from an 11th file to find group and department.

Finally we have to filter them by group and copy them all to their own file for each group.

This process takes 1 full day for 2 of us.

 

Currently in Power BI i have it set to look at the folder containing the 10 folders and creates a relationship to the eleventh file and it works! However this works for 1 week, so next week do i have to sort the data again from a new folder? Can Power BI auto-refresh based on folder data or do i need to publish it every week myself? Can Power BI read the .ZIP files without me having to extract them all first? Is it possible to store a count of old data for Trends?

 

Hopefully someone can help! Thank you.

Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    However this works for 1 week, so next week do i have to sort the data again from a new folder?

     

    If you put all folders in a folder A, then connect to this folder A with Power BI Desktop,

    Next time, just add the new folder in the Folder A, click "refresh all" inside Edit queries or "refresh" in Home menu.

     

     

    Can Power BI auto-refresh based on folder data or do i need to publish it every week myself?

    With schedule refresh, you don't need to publish it every week.

    You could download on-premise gateway, add folder under the gateway, then set schedule refresh time for the dataset.

    https://docs.microsoft.com/en-us/power-bi/report-server/configure-scheduled-refresh

     

    Can Power BI read the .ZIP files without me having to extract them all first?

    Previously, i provide a solution for this, it can work on my side, but not for customer.

    You could have a try.

    https://community.powerbi.com/t5/Power-Query/Trying-to-extract-files-from-ZIP/td-p/726169

     

    Is it possible to store a count of old data for Trends?

    If every week new file and folder replace the old file and folder, then after refresh, the new data update but old data loss, in this case, it is impossible totore a count of old data for Trends.

     

    If the old data still hold in the folder and exsit in the pbix file, you could store a count of old data for Trends.

    https://carldesouza.com/power-bi-year-over-year-using-sameperiodlastyear/

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello,

       

      I tried creating a folder and storing the others within, this works locally for displaying the updated data however does not update the data on the browser version of Power BI which is where i need the data to be displayed. I have to hit publish in order to update this data everytime or is there a way around this

       

      As for the Gateway i installed the personal version as i do not have admin rights to install the other one but there is no guides on how to actually connect the gateway to Power BI. It is installed and says it is running but i cannot figure out how to tell it which folder to be looking at or how to connect it to Power BI.