Home Forums Power Pivot Unpivot Data Model Table

This topic contains 5 replies, has 5 voices, and was last updated by  Freedup 1 month, 3 weeks ago.

Viewing 6 posts - 1 through 6 (of 6 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: 40
    • Total: 50

    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

    Attachments:
    You must be logged in to view attached files.
    #8038

    Freedup
    Participant
    • Started: 4
    • Replies: 6
    • Total: 10

     

    I see your problem… The first table can not have income category cause all three values are on same row.

    if you unpivot as Dan suggests then you can map “attributes” to category and form your report.

    OR create measures that “filter” for the correct statement category

    to me, unpivot would be easier to deal with

    just some thoughts

     

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.