Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamicaly named columns

Hi.

Is there any way how to dynamicaly name column in table or matrix visualisation?

I have simple table - revenue per month:

Month - 2019 - 2018 - 2017

January - 100 - 80 - 60

February - 120 - 120 - 100

...

Data source is generated as data for last 3 years. So in 2019 it's range 2017-2019, in 2020 it will be 2018-2020.

 

But when I name columns as exact years in 2020 I will need to rename it again.

Now i use syntax as:

current  year = YEAR (TODAY())...

current year-1 = YEAR (TODAY())-1...

current year-2 = = YEAR (TODAY())-2...

what is not very nice names of columns looks bad.

 

Any advise?

 

Thx.

 

Tips and Tricks

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous you just unpivot your data, in advanced editor, select month, right click and then choose unpivot other columns

     

    You will get two new colums, attribute and value and rename attribute to year. Apply the changes.

     

    Now add matrix visual, put month column on rows, year column on columns and value column on value, and you will have the result.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Is this problem sloved? 

    If it is sloved, could you kindly accept it as a solution to close this case?

    If not, please let me know.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-juanli-msft 

      I tried this solution, but it does't work. Maybe I do something wrong. So that's why I didn't accept solution. But if it's a problem I will accept it.

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        Could you share the screenshot of your original dataset when importing into Power BI?

        The example of your first post shows the correct data structure as below, right?

        month 2018 2019
        1 20 20
        2 20 20
        3 20 20

         

        I wonder how you update data (eg, from 2017~2019 to 2018~2020)

        do you add new column "2020" with data in the original file and refresh from Power BI to add this data in?

         

        Best Regards
        Maggie