table auto format

New Contributor

Hello everyone - 

I am trying to find out the best method to automate a report format. I would like to: 

1) Extract a spreadsheet from an application and format the table automatically (ie: remove some columns, format the columns, and move columns around etc). So when I get a new dataset, it will automatically format the new table. I started using excel power query to look in a folder for the new file, but not sure where to go from there to automate the formatting. 


I'm very familiar with doing this in Power BI, but I need to export the report to excel and would loose the formatting. Is a pivot table the only way?  I don't want to create calculations  etc. just format a new table of records. 


Thanks in advance! 




3 Replies

@jtpiazza If you can do it in Power BI, use Power Query in Excel.

In Power BI I can drag and drop the fields into a table in the order I want. I don't see how I can do that in Excel without creating a pivot table. Am I missing something?


In Excel you don't have visuals like in Power BI. What you may do that's reference the main query, adjust columns as needed and load result into the grid.