Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  Merged headers from excel to pbi

    Posted Dec 27, 2021 01:32 PM
    Hi guys, I hope you help me.


    I have a very unusual spreadsheet to take to Power BI to generate a year 2022 budget report.
    The problem is:
    My spreadsheet contains a top header named the current month, and below the header it splits into 3 columns. "Forecast", "Accomplished" and "Difference" column.
    Exemple:
    ENTERPRISES |                    JANUARY                         |                      FEBRUARY...                 |
    null                    | Forecast | Accomplished | Difference |  Forecast | Accomplished | Difference |

    How can I solve this problem? I've tried for M Language but, the answer wasn't satisfatory.

    ------------------------------
    Gustavo dos Santos
    TI Analist
    ------------------------------


  • 2.  RE: Merged headers from excel to pbi

    Silver Contributor
    Posted Dec 28, 2021 04:14 AM
      |   view attached
    Hi Guy,

    Please find attached an example very close to yours.
    You'll have to use different tools, Transpose, Unpivot Fill down...
    In my example you'll start from this, so like you...
    merged cells, and title is in 2 parts on two different lines :
    Excel fileto turn it into this :
    Power Query after transformations
    First I had to transpose, then promote headers, then fill in (to fill in merged cells) anf finally unpivot columns.
    For details please see attached files.

    Tell us if you found your way ;-)


    ------------------------------
    Olivier
    ------------------------------

    Attachment(s)

    zip
    FilesDemo.zip   49 KB 1 version
    Original Message