How to create a line chart from multiple excel sheets

[dankello]

Hi,

For student rewards (Bronze, Silver, Gold). I need to create a line chart which tracks progress over several weeks. Each week data is on a separate excel sheet. I want the line chart to look like this:

The data is on sheets like this:

How can I create this line chart in Power BI?

Thank you in advance 🙂

[Anonymous · Accepted solution]

Hi @dankello 

You can import the three tables to power bi, then create a calculated table

Table = var a=SUMMARIZE(ADDCOLUMNS('Week 1',"Week","Week1"),[Pupil],[Reward],[Week])
var b=SUMMARIZE(ADDCOLUMNS('Week 2',"Week","Week2"),[Pupil],[Reward],[Week])
var c=SUMMARIZE(ADDCOLUMNS('Week 3',"Week","Week3"),[Pupil],[Reward],[Week])
var d=GENERATEALL(GENERATEALL(SUMMARIZE('Week 1',[Pupil]),SUMMARIZE('Week 1',[Reward])),{"Week0"})
return ADDCOLUMNS(UNION(a,b,c,d),"Score",IF([Week]<>"Week0",SWITCH([Reward],"Gold",3,"Sliver",2,1),0))

Then create a measure

Measure = MAX('Table'[Score])

Then put the following field of the table to the line visual

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Data-estDog]

This solution provides exactly what the OP is asking for but perpetuates bad design and increases maintenance of the solution over time (you need to add a new sheet, new calculation, and modify the calculated table every turn of a week!!!!!!). If this is a one off throw away project or just a fictitiuos inquery to give you what you need for a real situation you have no power over, then use it. Otherwise invest an hour into chaning the source data design as show in my previous reply. You will be glad you did.

[Data-estDog]

Add a WeekNumber Column to sheet 1. Or better yet, a DATE

So columns look like....
Date, Pupil, Form, Reward

Combine all the data to a single sheet, adding the Date to each entry. 

Import to power bi. 

If I answered your question, please mark my post as solution, Appreciate your Kudo