Unpivot table created in the data model (not from query)

zapppsr

Board Regular
Joined
Aug 19, 2010
Messages
189
Hi.

I know how to make queries and during the process unpivot columns from a table, either on Power Query on Excel or Power BI.

I have a table result from a query, with only one column: "Regional". I added several other columns using "Add new column", so the new columns are not from the query, but created in Power Pivot or in the Model Editor on Power BI.


unpivot.png


Now, my goal is to unpivot the table above, with 3 columns: "Regional", "indicator", "Value".

The problem is that in the Model Editor in Power BI or in Power Pivot we don't have the unpivot option. So I was considering the following options:

1) - Find a way to use the Query Editor and get the full table (Not the query table because that one just have to column "Regional" and not the other.

2) - Find a way to create a Table (using the create table option) and in the process unpivot the data from the original table.

3) - Not elegant way: Plot the data using a Pivot table and make a query on that data, and then unpivot. (Shame on me).

Do you have any other options to help me?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi, ImkeF thanks for taking your time and replying.

The reason I'm not doing it in the Query Editor is that the data is not available in the Query Editor. The table I want to query only exists inside the model (Power Pivot our Power BI Table Editor).

I couldn't find a way to start a query and select a table created in Power Pivot/Power BI Table Editor. You know what I mean? There is a basic table, with only one column "Regional". I created this table querying the data source. After adding this table to the model, I added several other columns. Now I want to unpivot the table. and have only 3 columns.

I found a workaround using one of the techniques shown by Marco Russo, but I had to plot the table in a sheet in order to do that.

I went to Data, from Existing Sourced and selected the table I wanted to unpivot. When it plots the data, it brings not only the "Regional" column, but all other "virtual columns" added by formulas on the basic table.

After that, I queried this table and did what I wanted to do. The only inconvenient is that I have to add this table to a sheet to do it. Since it is a consolidation table and only have 12 lines, for this scenario it is OK.

I wish I could connect to the virtual table and query it. That is what this post is about.

Thanks again.
 
Upvote 0
I first did everything in Excel, so the issue was on Power Pivot.
After that, I imported the whole model to Power BI using the import tool, first. And there in Power BI the situation was the same: How to query a virtual table in the model.

Later, also in Excel that I generated my workaround table. Then i had another approach, instead of import the whole model, I imported only the workaround table and accomplished my goal.
 
Upvote 0
I don't see any other alternative than my previous suggestion at the moment.
But in Power BI everything needs to go through the query-editor, so why don't you do all your transformations in the query-editor there then?
 
Upvote 0
If you have written this with addcolumns, why not write multiple single column tables and use union to combine them all.
 
Upvote 0
If you have written this with addcolumns, why not write multiple single column tables and use union to combine them all.

I would, if they existed. I would if when I used the Query Editor I could find the table.

When you create a query, you can use many different data source, but not the tables created virtually in Power Pivot/Power BI, as far as I'm concerned.
 
Upvote 0
Here is the Workbook.

Instructions:

1) Go to Power Pivot and "Manage Data Model".
2) Find a table called dRegionais
3) Unpivot like this: Column 1: Regional, Column 2: Item, Column 3: Value.

Obs.: There will be mixed data types such as text and numbers in "Value", but don't worry about it, because in my workaround solution I did two queries one for each data type. But you don't need to do that. Just show me a way to unpivot without importing the data to a spreadsheet as I did.

And, Matt, are you THE Matt Allington? Business Intelligence, Power Pivot and BI guru? PS. Thanks for taking your time to help me and, I admire your work.
 
Upvote 0
I would, if they existed. I would if when I used the Query Editor I could find the table..

you are correct - you can't access tables created in dax/power pivot from power query. I assume the reason you you are using addcolumns is so you can use your measures - that's a good idea if you need to use measures. My point was that dax (not power query) has a union function

https://msdn.microsoft.com/en-us/library/dn802530.aspx

if you can write this
addcolumns(
Summarize(table, table[column1],table[column2]),
"measure 1", [measure 1],
"measure 2", [measure 2]
)

Then why not this?
Union(
Addcolumns(
Summarize(table, table[column1],table[column2]),
"attribute", "measure 1",
"Value", [measure 1]
),

Addcolumns(
Summarize(table, table[column1],table[column2]),
"attribute", "measure 2",
"Value", [measure 2]
)
)

And, Matt, are you THE Matt Allington? Business Intelligence, Power Pivot and BI guru? PS. Thanks for taking your time to help me and, I admire your work.

Thanks. Yes that's me. Glad to be able to help
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top