table auto format

%3CLINGO-SUB%20id%3D%22lingo-sub-2791738%22%20slang%3D%22en-US%22%3Etable%20auto%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2791738%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%20-%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20find%20out%20the%20best%20method%20to%20automate%20a%20report%20format.%20I%20would%20like%20to%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Extract%20a%20spreadsheet%20from%20an%20application%20and%20format%20the%20table%20automatically%20(ie%3A%20remove%20some%20columns%2C%20format%20the%20columns%2C%20and%20move%20columns%20around%20etc).%20So%20when%20I%20get%20a%20new%20dataset%2C%20it%20will%20automatically%20format%20the%20new%20table.%20I%20started%20using%20excel%20power%20query%20to%20look%20in%20a%20folder%20for%20the%20new%20file%2C%20but%20not%20sure%20where%20to%20go%20from%20there%20to%20automate%20the%20formatting.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20very%20familiar%20with%20doing%20this%20in%20Power%20BI%2C%20but%20I%20need%20to%20export%20the%20report%20to%20excel%20and%20would%20loose%20the%20formatting.%20Is%20a%20pivot%20table%20the%20only%20way%3F%26nbsp%3B%20I%20don't%20want%20to%20create%20calculations%26nbsp%3B%20etc.%20just%20format%20a%20new%20table%20of%20records.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2791738%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2791812%22%20slang%3D%22en-US%22%3ERe%3A%20table%20auto%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2791812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973449%22%20target%3D%22_blank%22%3E%40jtpiazza%3C%2FA%3E%26nbsp%3BIf%20you%20can%20do%20it%20in%20Power%20BI%2C%20use%20Power%20Query%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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! 

Jim

 

 

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?

@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.