Forum Discussion
Formatting for PowerBI
Hello! I have an excel sheet that I need to connect to a PowerBI - but it needs to be reformatted. I wasn't sure what the best way to format it is.
I have different tabs (all formatted the same way) for each year of data, and then I have a matrix created to have all of my data. I need to be able to input this into a PowerBI and report on Planned OR and Line Result for each Plant and Line and then have the date on the x-axis. (basically create trendlines/analysis for this data in an easier template)
I have attached the excel sheet - thanks in advance!!
Hi nattiej101
As commited tried to get 2016-2023 data
#1 2019-2023 was previously shared. Included as a separate sheet in PQ_Restructured_PlantLine2016-2023
=> Data source file: Source_OperationRateReport2019-2023#2 2016-2018 Included as sheet as a separate sheet PQ_Restructured_PlantLine2016-2023:
In October Operation Rate Report (DSM) sheet 2018 has info. from Apr 2013 - Dec 2018
- Exported that sheet to a new workbook
=> Data source file: Source_OperationRateReport2016-2018
- Duplicated the sheet
- Renamed it '2018 AFTER'
- Deleted all columns between Apr 2013 and Dec 2015 (too many inconsistencies* and #DIV/0!, #REF! errors)
- Filled a bunch of empty cells with NA (all colored in orange in the sheet)
- Created a specific query to import the data from sheet '2018 AFTER'* Going through that sheet I saw an (maybe there are others...) interesting =AVERAGE(H53,H55,H57,H53) that necessarily returns an inaccurate Avg...
+ Some values that seem to be Avg are not calculated and sometimes stored as Text values due to extra space(s) with the Number#3 Combined/Appended #1 & #2 as a single table named '2016-2023' in PQ_Restructured_PlantLine2016-2023
==> 5,564 records (0 errors)Hope this helps
Hi nattiej101
#1 I have different tabs (all formatted the same way)
Yes and then No. At the bottom of each sheet you have a couple of extra (inconsistent) rows and not at the same positions (row number). This prevents automating the restructuring process ==> extra rows will be deleted in the file you shared
#2 In reality how many sheets/years do you have to restructure?
If I were to delete those extra rows (they are just totals I believe) would that help? I have attached the newer/modified report.
I have about 10 years of data (2013-2023)
If I were to delete those extra rows (they are just totals I believe) would that help?
That's exactly what I said. It's actually a must, especially with 10 years to restructure
Attached is 2022 restructured. Could you check and confirm this reflects what you need per sheet/year?
If not, what (exactly) is missing/wrong?
- To prepare your Excel data for analysis in Power BI and create trendlines and analyses, follow these steps:
1. Structuring Your Data:
Consolidate Data:
Create a single table that consolidates the data from all years. This table should have columns for Year, Plant, Line, Date, Planned OR, and Line Result.
Format Date:
Ensure the Date column is formatted as a date in Excel.
2. Import Data into Power BI:
Open Power BI:
Open Power BI Desktop.
Get Data:
Click on "Home" > "Get Data" > "Excel."
Select Workbook:
Choose your Excel workbook and select the consolidated table.
3. Data Modeling:
Create Relationships:
If you have multiple tables (e.g., Plant and Line details), create relationships between them and the main data table.
Define Measures:
Create measures for aggregations like sums or averages based on your analysis needs.
4. Visualizations:
Line Chart:
Create a line chart with Date on the x-axis, Planned OR and Line Result on the values axis, and possibly using Plant or Line as legends.
Trendlines:
You can add trendlines to your line chart by selecting the chart, going to the "Analytics" pane, and adding a "Trendline."
Filters:
Add slicers or filters for Year, Plant, or Line to enable users to filter the data interactively.
5. Save and Share:
Save Report:
Save your Power BI report.
Share:
Share the report or publish it to the Power BI service for wider access.
Additional Tips:
Data Quality:
Ensure data quality and consistency across years and tabs.
Refresh Data:
Set up a data refresh schedule if your Excel data changes regularly.
Explore Power BI Features:
Explore other Power BI features like calculated columns, DAX (Data Analysis Expressions) for more complex calculations.
Remember, the specific details may vary based on your exact data and analysis needs. Adjust the steps as necessary, and leverage Power BI's features to create insightful visualizations and analyses based on your consolidated data.
