Create line diagram from excel sheet

[StanDaMan0505]

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.

[bullius · Accepted solution]

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.

[StanDaMan0505]

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]

Try this: Within Query Editor,

1. Select all of your Date columns

2. Select the "Transform" tab

3. Select "Unpivot Columns"

[StanDaMan0505]

Hi @bullius,

Thanks for the tip with the unpivot, but that did not work. It made some really strange table out of it 🙂

But I wrote now a VBA script that changes the data into the format you suggested and loaded that data into BI. That works beautiful and exactly the way I wanted. Thank you very much for your help!

What I dont understand is that it works flawlessly in Excel.. Shouldnt it be possible to make a similar diagramm with the same data in BI as in Excel?

Anway. Thank you and have a nice weekend.

Cheers