Forum Discussion
Formatting for PowerBI
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
I would like them to be null values That's what I anticipated 🙂
Attached file contains what you need to combine your sheets, assuming:
- They're named 2013, 2014... 2023 (a Numeric name)
- You have deleted all the extra rows at the bottom of each sheet
#1 Go the the PARAM sheet and update the file path with yours (path to the file containing sheets 2013, 2014, .... 2023)
#2 Switch to sheet PlantsLines
#3 Go to Data (tab) > Queries & Connections (the corresponding pane opens on the right)
#4 Right-click in the green table (still in sheet PlantsLines) > Refresh
then wait and see...
When the query refresh is complete, in the Queries & Connections pane, check that no error is reported under query name PlantLines. This should look like (w/diff. #rows of course):
If you have errors you can follow this article to understand the reasons and hopefully fix them yourself. Worst case scenario upload your file and I'll have a look at it (I'll be off in +/- 1h from now)
Here is the error that I'm getting:
I have even attached the file I was trying to connect to with all of the tabs.
Glad I could help nattiej101 . Thanks for posting back & Nice EOY...
You are incredible and deserve a raise!!! Thank you so so much. You just saved me probably 40 hours worth of reorganizing these files.
I hope you have a blessed rest of your year!! Lorenzo
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
2016-2023: Extra rows at the bottom were not deleted
2014-2018: Have empty columns in-between others:
In the meantime 2019-2023 with no error is attached
You're not serious 😉 Not all your sheets are formatted the same way - very quick 1st analysis...:
- Nothing in A1-C1 (easy to fix) in some sheets
- 2011-2013 have extra empty rows in the middle
- Before 2016 some columns are not in the same order as what you shared before or even don't exist
...
#1 I'll try to fix issues in 2016-2023 myself as they look quite consistent but are execptions obviously as I get a bunch of errors with them
#2 For 2014-2015 reorder (& rename) the columns so they're in the same order as what you previously shared (and reshare your file) please
#3 For 2011-2013 either forget about those years or create columns (in the right order+name) with dummy data
