Forum Discussion
Create line diagram from excel sheet
Hi,
I'm new to BI, but played around and tried some basic functions. They all look beautiful and easy. But now I wanted to created a "real world" scenario for our company and now I'm struggeling.
I have an Excel Worksheet.
In the first row (starting from B1) are the dates (B1=2017-03-05, C1=2017-03-07 and so on).
In the first column (starting from A2) are the "database names" (A2=DB1,A3=DB2 .... A14=DB13 and so on).
In the fields following the "database names" are the values I'm interessted in (field B2=1.03. C2=1.05 and so on, the size of the database).
Now I would like to create a "line diagram". In Excel it's like two clicks and its done. But because I have over 100 databases and around 50 dates it's too much information for an Excel diagram.
In BI Desktop, I import the data from the excel sheet.
Then I change the query, so that the "first row is used as title", save and go back to my report-view. At the right hand side, it shows now all the fields (with the dates and the column with the names)
When I then choose the line diagram and try to add the fileds to values and axis, I dont get the result I expect..
I would like to get a diagram that in the x-axis has the dates and in the y-axis the values, so I can see the growth of a database over time.
I'm quite sure that this is eassily possible, but can't find the solution to it.
Any help is appreciated.
Hi StanDaMan0505,
I suspect from what you have said, that you need to re-model your data to look something like this:Database Name Date Size DB1 05/03/2017 1.03 DB1 06/03/2017 1.05 DB2 05/03/2017 1.03 DB2 06/03/2017 1.05 DB3 05/03/2017 1.03 DB3 06/03/2017 1.05 Then put Date as the x-axis, Size as the Values, and Database Name as the Legend.
4 Replies
- bullius
Helper V
Hi StanDaMan0505,
I suspect from what you have said, that you need to re-model your data to look something like this:Database Name Date Size DB1 05/03/2017 1.03 DB1 06/03/2017 1.05 DB2 05/03/2017 1.03 DB2 06/03/2017 1.05 DB3 05/03/2017 1.03 DB3 06/03/2017 1.05 Then put Date as the x-axis, Size as the Values, and Database Name as the Legend.
- StanDaMan0505New Member
Hi bullius,
Thank you for your reply.
Is there a way to let Power BI do that? :-)
Don't want to do that manually, because its over 100 databases and around 50 dates. Thats quite a few fields... Need to write a script for that.
Or do you have any other suggestions?
Regards
Stan
- bullius
Helper V
Try this: Within Query Editor,
1. Select all of your Date columns
2. Select the "Transform" tab
3. Select "Unpivot Columns"