Forum Discussion
Helper VCreating a newer table based on data of existing calculated table.
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
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
7 Replies
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
bolabugaThks 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
Super User
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
Memorable Member
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.
