Forum Discussion

megavic's avatar
megavic
Advocate II
10 years ago
Solved

Line chart using a table that has multiple fields for x axis

Hi - new Power BI adopter here! I have a table that I'd like a simple line graph for. The issue I have is that the values for the x axis are in individual fields in the table, e.g. PeriodBalance1, P...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    Ya, the "shape" of your data is not helping you.  In the query update window thingy (power query) -- check out the UNPIVOT feature, that would change your life.

megavic's avatar
megavic
Advocate II
10 years ago

Hi Lydia,

 

Many thanks for your speedy response!

 

I understand everything you've said, and I am already showing the data in a matrix - I did however want to show the data at the same time in a simple line graph. I'm thinking I could perhaps pull all the data in to a single field in access. Is there anything I could do in power BI query to move the data in to a single field?

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hi megavic,

You can consider to create a calculated column to sum the values of these fields, then create Line chart by putting Month into X-axis and dragging the new column into Values and dragging Year into Legend.

For example: column = Test[Period1]+Test[Period2]+Test[Period3]+Test[Period4]+Test[Period5]+Test[Period6]+Test[Period7]




Thanks,
Lydia Zhang

  • megavic's avatar
    megavic
    Advocate II
    10 years ago

    Thanks Anonymous,

     

    I think the issue with I have with that idea is that I don't have a 'month' field.

     

    I'll have a look at what I can do to manipulate the data before it gets in to Power BI.

     

    Thanks again,

     

    Andy

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Ya, the "shape" of your data is not helping you.  In the query update window thingy (power query) -- check out the UNPIVOT feature, that would change your life.

      • megavic's avatar
        megavic
        Advocate II
        9 years ago

        Oh that is sooooo good :smileysurprised:

         

        That completely reshapes the query table data and is so simple but powerful!

         

        Thank you very much for the suggestion, this is working perfectly.

         

        Much appreciated - kudos and accepted answer :smileyhappy: