Home Forums Power Pivot Unpivot Data Model Table

This topic contains 4 replies, has 4 voices, and was last updated by  Dan Bliss 6 days, 11 hours ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #7748

    BKaufmann
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    I am producing different sets of reports from the same data model.

    My data model has a table that contains multiple columns.  Three of the columns are dollar amounts, and this works fine for the first set of reports.

    For the second set of reports, however, I need all three dollar amounts in the same column.  For example:

    Currently the column headings look like this:

    Date  |  ID  |  Category |  Revenue Amount  |  COGS Amount  |  Gross Margin Amount

     

    What I want is this:

    Date  |  ID  |  Category |  Amount  |  Income Statement Category

     

    The only two ways I can think of to do this is to either use Power Query to unpivot the data – which isn’t possible – or to save the Data Model table as a spreadsheet file, then use Power Query to unpivot the data – which is also not possible.

    Is there a way to do what I’m seeking to do?

    Thank you,

    Bruce

     

    #7765

    porter444
    Participant
    • Started: 10
    • Replies: 39
    • Total: 49

    What’s the issue with using Power Query to unpivot? – you mentioned it’s not possible?

    #7784

    m3tr01d
    Participant
    • Started: 0
    • Replies: 15
    • Total: 15

    Hi,

    Can you tell me what are the relations between the columns

    Amount  |  Income Statement Category

    and

    Revenue Amount  |  COGS Amount  |  Gross Margin Amount

    This would help me to understand your problem.

    Thanks

    #7796

    BKaufmann
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hello.  Sorry for the delay in responding.  I was unable to access the forum for a couple of weeks.

    I know how to use Power Query to unpivot an Excel table.  What I was hoping to do was to use Power Query to unpivot a table that was contained within a data model.

    Subsequent research led me to the conclusion that, at the present time, you cannot use Power Query to unpivot a table contained within a data model.

    So I ended up doing what I was trying to avoid:  Going to the original source table and doing an unpivot on that.

    Thanks very much for your offer of assistance.

     

     

    #7991

    Dan Bliss
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hey BKaufman,

    You should not have any problem unpivoting the columns as you describe, so long as you have created a query within Power Query.  Perhaps you’ve done something somewhat different.  Loading a table to a data model will not affect the ability of PQ to unpivot the table.

    If you have done something like add a Calculated Column to a table loaded into your data model, and you want to unpivot the resultant table using PQ, you must first load the resultant table to a worksheet and create a new query based on the resultant table on a worksheet.  This is awkward and confusing, but I know of no way around it.  A naming convention that helps follow the trail of steps may help, such as:

    myTable, myTable_1, myTable_2, etc.

    Here is my result, after creating a table and query –  duplicating your described table schema & sample data:

    Date ID Category Rev COGS GM
    1/4/2017 1 NE 4230 3850 380
    3/6/2017 345 SW 3487 2483 1004
    4/8/2017 76 Central 784 874 -90
    5/2/2017 24 NE 329 123 206

    let
    Source = NotUnpivoted,
    #”Unpivoted Columns” = Table.UnpivotOtherColumns(Source, {“Date”, “ID”, “Category”}, “Attribute”, “Value”)
    in
    #”Unpivoted Columns”

    Date ID Category Attribute Value
    1/4/2017 0:00 1 NE Rev 4230
    1/4/2017 0:00 1 NE COGS 3850
    1/4/2017 0:00 1 NE GM 380
    3/6/2017 0:00 345 SW Rev 3487
    3/6/2017 0:00 345 SW COGS 2483
    3/6/2017 0:00 345 SW GM 1004
    4/8/2017 0:00 76 Central Rev 784
    4/8/2017 0:00 76 Central COGS 874
    4/8/2017 0:00 76 Central GM -90

    • This reply was modified 6 days, 11 hours ago by  Dan Bliss. Reason: fix formatting
    Attachments:
    You must be logged in to view attached files.
Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.