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



## zapppsr (Feb 22, 2017)

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*.








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?


----------



## ImkeF (Feb 24, 2017)

You can hack your current PBI model and access the table in the query editor again like described here: How to hack yourself in Power BI (and Power Pivot?) – The BIccountant but it's a hack und unsupported.

So far I haven't seen a case where you couldn't create columns in the query editor but in DAX. What is you reason for not doing it in the query editor?


----------



## zapppsr (Feb 24, 2017)

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.


----------



## ImkeF (Feb 24, 2017)

Are you doing this in Excel or in PowerBI?


----------



## zapppsr (Feb 24, 2017)

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.


----------



## ImkeF (Feb 25, 2017)

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?


----------



## Matt Allington (Feb 25, 2017)

If you have written this with addcolumns, why not write multiple single column tables and use union to combine them all.


----------



## zapppsr (Feb 26, 2017)

Matt Allington said:


> 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.


----------



## zapppsr (Feb 26, 2017)

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.


----------



## Matt Allington (Feb 28, 2017)

zapppsr said:


> 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*


----------



## zapppsr (Feb 22, 2017)

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*.








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?


----------



## zapppsr (Feb 28, 2017)

Wow, that's nice, the real Matt Allington!

So, let me see if I got it...

You are suggesting to use UNION, ADDCOLUMNS and SUMMARIZE to unpivot my virtual table, using DAX.
I have to write ADDCOLUMNS, SUMMARIZE for each measure I have right?

But where will the new unpivot table be generated? In Power BI there is the New Table function, in Power Pivot I'm not sure where to do it.

Additionally, some columns of dRegional table are pure measures repeated as column, but other are created there using the "measure" columns to do additional calculation... So, can I use that columns instead of the measures to use ADDCOLUMNS and SUMMARIZE?

I know this model is complicated, but it was the only way I managed to do all calculations I needed.

I have a workaround, which is not elegant but is fine, because I inserted the dRegionais table on a spreadsheet via data import and queried that...
So is more a learning thing now that an urgent solution.

And since I have the opportunity to talk to one of the most respected names in the area I will try to learn new things.

Thanks again!


----------



## Matt Allington (Mar 1, 2017)

I thought you were using the ADDCOLUMNS feature of DAX, but now I notice (upon reflection) that you are using the manual Add Column feature.  So my previous comment doesn't really apply for Power Pivot.  There may be a way but rather than work on a hack let's spend some time exploring the best solution.  Is it possible is to go back to Power Query and add the columns there instead of doing it inside Power Pivot?  see my best practices here. Best Practices for Power Pivot, Power Query and Power BI - Excelerator BI 

 Is that possible?   What are the issues with doing this?


----------



## zapppsr (Mar 1, 2017)

Hi, Matt, thanks for keeping up the discussion!

I can't see a way to add the columns in Power Query due the nature of the model I created, first calculating measures, then adding those measures in columns to use them do make further calculations in the table.
It is a rather customized and manual approach, but it is way better than doing it in spreadsheets and cells. 

It is working fine, since I was able to insert the dRegionais table in a spreadsheet using data import. 
So the total process to update the data and publish to Power BI is possible in under 1 minute, which is fine for me.

I was just looking for a possibility to query a table that only exist virtually in the model, thus not being able to be queried. But as far as we discussed, this is not a natural implementation yet.

Thank you again, it is an honor to have you looking into my doubts!


----------



## Matt Allington (Mar 1, 2017)

How big is your final table in  Power Pivot?  ie how many columns and how many rows?

If you go back to Power BI, you can do it using the original suggestion I mentioned (using ADDCOLUMNS, SUMMARIZE, etc).  You just create the query inside a "New Table".  I think there is a way to do it in Excel too, but it would mean you would have to first materialise the table in Excel (not Power Pivot) and reload it to Power Pivot.  That would mean you would have an uncompressed copy of the table in Excel, and that may be an issue.  It depends on how big the table is (hence my opening question)


----------



## zapppsr (Mar 2, 2017)

Hi Matt:

The table itself is very small, thus why my workaround was to "materialize" the table in Excel, and run a query on it, unpivoting the data in the process. Although not the more elegant way, it serves my purpose and when there is new data, the whole processos from updating and publishing to Power BI is less than 1 minute.

I will keep it like this because its a very closed scenario used by a few people.

Thanks again and glad having your kind of knowledge available here to help us.


----------

