Forum Discussion

StanDaMan0505's avatar
StanDaMan0505
New Member
9 years ago
Solved

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 NameDateSize
    DB105/03/20171.03
    DB106/03/20171.05
    DB205/03/20171.03
    DB206/03/20171.05
    DB305/03/20171.03
    DB306/03/20171.05

     

    Then put Date as the x-axis, Size as the Values, and Database Name as the Legend.

4 Replies

  • Hi StanDaMan0505,

    I suspect from what you have said, that you need to re-model your data to look something like this:

     

    Database NameDateSize
    DB105/03/20171.03
    DB106/03/20171.05
    DB205/03/20171.03
    DB206/03/20171.05
    DB305/03/20171.03
    DB306/03/20171.05

     

    Then put Date as the x-axis, Size as the Values, and Database Name as the Legend.

    • StanDaMan0505's avatar
      StanDaMan0505
      New 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's avatar
        bullius
        Icon for Helper V rankHelper V

        Try this: Within Query Editor,

         

        1. Select all of your Date columns

        2. Select the "Transform" tab

        3. Select "Unpivot Columns"