The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello:
I have 12 tables, each for one month. Here you have an example of two months. In the first column I have name and surname of each person. In following columns, I have different concepts that have earned all the days in the month. There was an additional day row that has been hidden. This is the reason because concepts are repeated en each month. Each one was earned in different day.
I need to sum the total of year of each concept per person. The conpcets can be different in each month. Anyobody knows how to calculate it?
Thanks in advance.
An example:
The desired result:
Year
Junta Govern Local | Comissió Interàrees | |
Person 1 | XXX | YYY |
Person 2 | ZZZZ | KKKK |
Data I have:
January
Junta Govern Local | Comissió Interàrees | Comissió Interàrees | Consell Escolar Mpal | Junta Govern Local | Comissió Interàrees | Comissió Informativa P | Junta de Portaveus | Comissió Interàrees | Consell Comerç i Consum | Junta Govern Local E | Ple | |
Person1 | 61.48 | ABSENT | ABSENT | null | 61.48 | ABSENT | 112.7 | null | 175.24 | null | 61.48 | 276.62 |
Person2 | null | 175.24 | 175.24 | null | null | 175.24 | 112.7 | null | 175.24 | 112.7 | null | 276.62 |
Person3 | 61.48 | 175.24 | 175.24 | null | 61.48 | 175.24 | 112.7 | null | 175.24 | null | 61.48 | 276.62 |
Person4 | 61.48 | ABSENT | 175.24 | 112.7 | 61.48 | ABSENT | ABSENT | 61.48 | 175.24 | null | 61.48 | 276.62 |
Person5 | null | 175.24 | 175.24 | null | null | 175.24 | 112.7 | null | 175.24 | null | null | 276.62 |
Person6 | null | null | null | null | null | null | 112.7 | 61.48 | null | null | null | 276.62 |
Person7 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
Person8 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
Person9 | null | null | null | 112.7 | null | null | 112.7 | null | null | 112.7 | null | 276.62 |
Person10 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
Person11 | 61.48 | 175.24 | 175.24 | 112.7 | 61.48 | 175.24 | 112.7 | 61.48 | 175.24 | null | ABSENT | 276.62 |
Person12 | null | 175.24 | 175.24 | null | null | 175.24 | 112.7 | null | 175.24 | 112.7 | null | 276.62 |
Person13 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
Person14 | null | null | null | 112.7 | null | null | 112.7 | 61.48 | null | 112.7 | null | 276.62 |
Person15 | null | null | null | 112.7 | null | null | 112.7 | 61.48 | null | 112.7 | null | 276.62 |
February
Cognoms i nom | Junta Govern Local | Comissió Interàrees | Comissió Interàrees | Junta Govern Local | Comissió Informativa P | Comissió Interàrees | Junta de Portaveus | Ple | Comissió Interàrees |
Person1 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | null | 276.62 | 175.24 |
Person2 | null | 175.24 | 175.24 | null | 112.7 | 175.24 | null | 276.62 | 175.24 |
Person3 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | null | 276.62 | 175.24 |
Person15 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | 61.48 | 276.62 | 175.24 |
Person18 | null | 175.24 | 175.24 | null | 112.7 | 175.24 | null | 276.62 | 175.24 |
Person19 | null | null | null | null | 112.7 | null | ABSENT | 276.62 | null |
Person20 | null | null | null | null | 112.7 | null | null | 276.62 | null |
Person5 | null | null | null | null | 112.7 | null | null | 276.62 | null |
Person6 | null | null | null | null | 112.7 | null | null | 276.62 | null |
Person7 | null | null | null | null | ABSENT | null | null | 276.62 | null |
Person8 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | 61.48 | 276.62 | 175.24 |
Person9 | null | 175.24 | 175.24 | null | 112.7 | 175.24 | null | 276.62 | 175.24 |
Person10 | null | null | null | null | 112.7 | null | null | 276.62 | null |
Person11 | null | null | null | null | 112.7 | null | 61.48 | 276.62 | null |
Person12 | null | null | null | null | 112.7 | null | 61.48 | 276.62 | null |
Hello @francescjp ,
i would suggest at first to unpivot the 12 tables :
example of unpivoting a table :
after unpivoting :
after you unpivot all tables, the next step is to append tables ( use append query in the power query ) .
third step :
create a matrix with concept on the columns and person on the rows and aggregation on values .
hope this helps
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
124 | |
111 | |
78 | |
78 |