table auto format

[jtpiazza]

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! 

Jim

[#2]

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

[#3]

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?

[#4]

@jtpiazza 

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.