Forum Discussion

nattiej101's avatar
nattiej101
Brass Contributor
Nov 16, 2023

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...
BI & Data Analysis
Excel
Power BI
  • Lorenzo's avatar
    Lorenzo
    Nov 17, 2023

    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

Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 16, 2023

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?

  • nattiej101's avatar
    nattiej101
    Brass Contributor
    Nov 16, 2023

    Lorenzo 

     

    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)

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Nov 16, 2023

      nattiej101 

      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?

      • nattiej101's avatar
        nattiej101
        Brass Contributor
        Nov 16, 2023
        That looks perfect - how did you do that/how can I recreate that for all of the other years?

Resources