Creating a newer table based on data of existing calculated table.

[bolabuga]

Hello, i have the table above.

i would like to create a newer table using the results of the last 6 columns, to be able to make a graphic of the years 2015 and 2016, im not managing to construct it. The table would look like:

header (m³ / econ)                                             header (year)

value (m³ / econ condominions 2015)                     2015

value (m³ / econ resid unifamiliar 2015)                  2015

value (m³ / econ res 2 a 7 2015)                              2015

value (m³ / econ condominions 2016)                     2016

value (m³ / econ resid unifamiliar 2016)                  2016

value (m³ / econ res 2 a 7 2016)                              2016 

[v-shex-msft · Accepted solution]

Hi @bolabuga,

You can also use union function and row function to get the specify records form a specify table:

Table = UNION(

ROW("m³ / econ",MAX(Table1[m³ / econ condominions 2015]),"AND","2015",”DESCRAMO”,”condominions”),

ROW("m³ / econ",MAX(Table1[m³ / econ resid unifamiliar 2015]),"AND","2015",”DESCRAMO”,”resid unifamilia”),

ROW("m³ / econ",MAX(Table1[m³ / econ res 2 a 7 2015]),"AND","2015",”DESCRAMO”,”econ res 2 a 7”),

ROW("m³ / econ",MAX(Table1[m³ / econ condominions 2016]),"AND","2016",”DESCRAMO”,”condominions”),

ROW("m³ / econ",MAX(Table1[m³ / econ resid unifamiliar 2016]),"AND","2016",”DESCRAMO”,” resid unifamilia”),

ROW("m³ / econ",MAX(Table1[m³ / econ res 2 a 7 2016]),"AND","2016",”DESCRAMO”,” econ res 2 a 7”)

)

Regards,

Xiaoxin Sheng

[bolabuga]

Thks Xiaoxin, both you and cahaba for the replys.

Your suggestion did exactly what i wanted, realllly thks. the screen of the solution on my side, applying the command chain suggested by xiaoxin.

[BhaveshPatel]

You can use powerquery to the rescue. Import your data and filter the year column 2015 & 2016, delete the unncessary columns and  Load. You can rename the table.

Not sure is this what you want to achieve.

[bolabuga]

I want to achieve the result above on the screenshots, but i what i did here was create on the excel the table and imported it on powerbi. 

I have the results on powerbi, in a table, but as i was asking, i dont know how to use them to mount a newer table to be able to make a graphic just like that.

However, im really new into PBI, i will research what "powerquery" can do for me :).

[CahabaData]

Your starting format has the data side-by-side, and in the Query Editor feature of Power BI you can unpivot/transform the layout to what you seek.  There is actually a video example of this in the Learning area.

Those transform steps are saved, so that when you refresh your data you do not have to manually restructure anything again.

[bolabuga]

Hello Cahaba, my calculated columns are not showing on the query editor, i tried to do the same calculation on a newer column, but i couldnt find a place to use the same command format that i used on the columns on the screen of my first post.

Hum i found some explanations of pivot and unpivot, i think i understand a little bit more, i could duplicate my table, delete unnecessary data, and unpivot the columns in rows. now its just a matter of learn why my calculated columns are not showing on the query editor :(:(.

[CahabaData]

ah I thought your image was your starting data.  the calc column feature occurs after the query editor... so they won't show.  do your query editing transforms, then do your calc columns using the tables from QE.