This topic contains 5 replies, has 5 voices, and was last updated by 
-
Posts
-
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
What’s the issue with using Power Query to unpivot? – you mentioned it’s not possible?
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
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.
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 206let
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 -90I 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
The forum ‘Power Pivot’ is closed to new topics and replies.