none
Using multiple rows as headers RRS feed

  • Question

  • I have data that is formatted like this:

    Row 1: An accounting line item e.g. Revenue, COGS, R&D

    Row 2: A financial year e.g. FY15, FY16, FY17

    Row 3: Company name and region

    Example data table looks like this:

                                           Rev     Rev      Rev     COGS   COGS  COGS   R&D     R&D     R&D ......

                                          FY15    FY16    FY17    FY15    FY16    FY17    FY15    FY16    FY17 ......

    Company Name  Region

    Xxx                   Xxxx      $xxM    $xxM   $xxM   $xxM    $xxM   $xxM   $xxM    $xxM   $xxM ......

    Xxx                   Xxxx      $xxM    $xxM   $xxM   $xxM    $xxM   $xxM   $xxM    $xxM   $xxM ......

    Xxx                   Xxxx      $xxM    $xxM   $xxM   $xxM    $xxM   $xxM   $xxM    $xxM   $xxM ......

    and so forth.

    My question is, how can I get Power BI to read in the data, so that I can then choose any combination of accounting line item, financial year, company and region?


    Monday, March 26, 2018 11:13 PM

Answers

  • Hi.

    Usual technique for this is:

    1. both two heading rows should be the rows in the table, not header
    2. Transpose table
    3. Select two first columns, Transform -> "Merge columns" with delimiter (for example, "=")
    4. transpose table
    5. Make first row a table header
    6. Select two first columns ("row headers"),
    7. Transform -> Unpivot -> (or just right-click on the selected columns) and Unpivot other columns
    8. Split column "Attribute" by your delimiter
    9. Rename columns as desired

    If your table is too big, then you can change these steps a bit to improve performance

    • add query (Q1) to the source table
    • select first two rows (for example, filter by the [Company Name] = null)
    • apply steps 2-4 from above
    • add another query (Q2) to the source table
    • remove two first rows (for example, filter by the [Company Name] <> null)
    • append Q2 to Q1 so the 1st row of the new table will be the one row from Q1.
    • apply steps 5-9 from above


    Maxim Zelensky Excel Inside

    Wednesday, March 28, 2018 4:07 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    UnPivot with PQ.
    http://www.mediafire.com/file/t8lk4ts4efloorw/03_26_18a.xlsx
    http://www.mediafire.com/file/tfso51orww5f8ih/03_26_18a.pdf

    Tuesday, March 27, 2018 1:29 AM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    UnPivot with PQ.
    http://www.mediafire.com/file/t8lk4ts4efloorw/03_26_18a.xlsx
    http://www.mediafire.com/file/tfso51orww5f8ih/03_26_18a.pdf

    Tuesday, March 27, 2018 1:29 AM
  • Hi.

    Usual technique for this is:

    1. both two heading rows should be the rows in the table, not header
    2. Transpose table
    3. Select two first columns, Transform -> "Merge columns" with delimiter (for example, "=")
    4. transpose table
    5. Make first row a table header
    6. Select two first columns ("row headers"),
    7. Transform -> Unpivot -> (or just right-click on the selected columns) and Unpivot other columns
    8. Split column "Attribute" by your delimiter
    9. Rename columns as desired

    If your table is too big, then you can change these steps a bit to improve performance

    • add query (Q1) to the source table
    • select first two rows (for example, filter by the [Company Name] = null)
    • apply steps 2-4 from above
    • add another query (Q2) to the source table
    • remove two first rows (for example, filter by the [Company Name] <> null)
    • append Q2 to Q1 so the 1st row of the new table will be the one row from Q1.
    • apply steps 5-9 from above


    Maxim Zelensky Excel Inside

    Wednesday, March 28, 2018 4:07 PM