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

[zapppsr]

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]

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]

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]

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]

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]

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.