Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

automate editing process to modify excel file

Hi guys, I',m facing an issue that I think have a simple solution, but I prefer to ask you and be sure.

 

every day an excel file is sent from an external source to me (so I cannot change the structure it has before it arrives on my desktop).

 

I need to perform some automations on this file (like delete a column, merge others, do some calculations) and save the file to send it then to others. some of the calculations that I need involve data that comes from a sql databse. I'm able to gather all the data and put it into power BI but what is the best way to do various calculus? is it better to use M code (so load and modify the data) or  DAX, (so create a table and extract it into excel)?

 

thanks a lot!

 

 

Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • SivaMani's avatar
    SivaMani
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    Anonymous , It depends on the requirement. If the calculation is related to the Analysis of the prepared data, go for DAX. If the calculation is related to data preparation, use M Query.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      yeah it is relatet to the analysis of the data. But if every day the file is sent as new file, how could I replace the file so that powerBI uses every day fresh new data?

      • SivaMani's avatar
        SivaMani
        Icon for Resident Rockstar rankResident Rockstar
        4 years ago

        Anonymous, You could keep the file in OneDrive with the correct format and replace it with the latest file. Connect Power BI to OneDrive and build the model/reports

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team _ kalyj