Forum Discussion

dajuju's avatar
dajuju
Frequent Visitor
5 years ago
Solved

Non tabular Excel report to formatted table

I get daily sales reports from multiple branches in Excel but in a reporting format that is not a standard table. Basically it's a page layout that looks good for printing but is useless as it is for data manipulation in Excel or PowerBI. 

 

I'm looking for a way to automate this so that I don't have to do this manually for every single branch, every single day.

 

Is there a way to convert this kind of data to a standard table? All the formatting is the same everyday, all the same information is in the same cells, I just don't know how to convert that to a table that I can then use for further work.

 

I'm not a VBA programmer so I'm sure there's a way to do this via VBA. I'm wondering if there's a way to do this with Power Query.

Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    5 years ago

    Hi dajuju 

    there is no need to transform it to a proper table object in Excel. You can work on simple sheet data as well.

    Just do the transformations in Power Query and all the steps you did will be applied to the new data once the query is refreshed.

     

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    5 years ago

    Hi dajuju 

    there is no need to transform it to a proper table object in Excel. You can work on simple sheet data as well.

    Just do the transformations in Power Query and all the steps you did will be applied to the new data once the query is refreshed.

     

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    5 years ago

    Hi dajuju ,

     

    ImkeF  is right.  If you tried what she suggested successfully, please accept the answer as a solution. Others who have the same questions will benefit from this thread. Thanks!