Create a Master sheet from reports that you receive everyday

I receive Covid-19 patients report everyday that I save in a folder. These reports have similar data-set and I have to create a consolidated report using these multiple files.

How I do: I have to manually save each report in a folder than copy paste its data to a Master sheet which takes considerable amount of time.

How can I do it better: You can create a Master Sheet with Power Query in Microsoft Excel and Power BI that consolidates your excel/ csv file reports at one plae and also provides option like Refresh. Power Query comes with the feature of Refresh that you can use to either refresh your Master Sheet data to update changes in your existing reports or to update data of new file from your Master folder.

To know how to do above action, click on the tab according to your software preferences

ABOUT: With Power Query, you can search for data sources, make links, and then form the data in ways the suit your needs (e.g., delete a column, alter a data type, or merge tables. You can share your findings once you have formed your data, or use your question to generate reports.

• Find and connect data through a large array of sources.
• Combine and shape data sources to suit your data analysis requirements or prepare them through software such as Power Pivot and Power View for further study and modeling.
• Creates custom views about the results.
• Perform operations to clean the records.
• Import the data from a variety of log files.
• Pull the data into Power Pivot as refresh connections from new data sources such as XML and File Folders.

Microsoft Excel Pro tip:

You can create a chart and required insights by consolidating all excel files within a folder.

STEP 1: Open Microsoft Excel, and click Data > Get data > Frome File > From Folder. Select folder that contains your excel files.

STEP 2: Click Combine > Combine & Transform Data. Combine Files window opens, click OK. A Power Query Editor window opens. Shape the data according to your need. Click Close & Load.

STEP 3: Convert your table to pivot table and create a graphical report from the consolidated data.

ABOUT: Power BI is a Microsoft corporate analytics service. It is intended to create a simple interface for end users to create their own reports and dashboards for interactive visualization and business intelligence capabilities.

Power BI Pro Tip:

Microsoft Power BI can create a chart and required insights by using your excel data.

STEP 1: Open POWER BI, and click Get data > Folder. Select folder that contains your excel files.

STEP 2: Click Combine and Transform data and click OK. A Power Query Editor window opens. Shape the data according to your need. Click Close & Apply.

STEP 3: Do one of the following:

• Click (Chart): To create different types of chart from your consolidated data
• Click (Table): To view your consolidated table and corresponding data

Labels