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....
Need Help
Tips and Tricks
  • bullius's avatar
    bullius
    9 years ago

    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.

bullius's avatar
bullius
Icon for Helper V rankHelper V
9 years ago

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
    9 years ago

    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
      9 years ago

      Try this: Within Query Editor,

       

      1. Select all of your Date columns

      2. Select the "Transform" tab

      3. Select "Unpivot Columns"

      • StanDaMan0505's avatar
        StanDaMan0505
        New Member
        9 years ago

        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