Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rows transformation to columns

Hello Guys,

 

I have one column with mixed date and year. I need to convert into row format where each column representing a different column with different Month and Year.  Please see the attached picture:Date _ICD column have Mixed month and Date from previous year (2020) and latest year(2021°)From PBI, I need to convert that Date_ICD column into something like this. Jan2020, Feb 2020,March2020 etc

 

Can someone tell me how can I do that in Power BI ?

PowerBI Server
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    It is suggested to create another table like below:

    Order Columns
    1 202001
    2 202002
    ...

    ...
    12

    202012
    13

    2020-Total
    14

    202101
    ...

    ...
    25

    202112
    26

    2021-Total
    ...

    ...

     

    And then, sort "Columns" column by "Order" column and put "Columns" column into Columns field of the Matrix visual. Then, create a measure like below and put it into Values field:

    Measure =
VAR Year_ =
    LEFT ( MAX ( 'Data'[DATE_ICD] ), 4 )
VAR Columns_ =
    MAX ( 'Table'[Columns] )
RETURN
    IF (
        Columns_ = "2020-Total",
        CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2020" ),
        IF (
            Columns_ = "2021-Total",
            CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2021" ),
            CALCULATE ( SUM ( 'Data'[Amount] ), 'Data'[DATE_ICD] = Columns_ )
        )
    )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

     

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Here's what I would do:

    in Power Query add a date column that represents the first day of the month.

     

    = Table.AddColumn(#"Renamed Columns", "Day", each Date.FromText(Text.Start([DATE_ICD],4) & "-" & Text.End([DATE_ICD],2) & "-01"))

     

    Then you can format that column in Power BI to anything you want, including MMM.YY

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi lbendlin ,

       

      I tried to use the same formula in my query editor but it throwing an error. Could you tell me what I am doing wrong here?

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    You can also create a calculated column like so:

    Column =
CONVERT (
    LEFT ( [Date_ICD], 4 ) & "-"
        & RIGHT ( [Date_ICD], 2 ) & "-01",
    DATETIME
)

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    You need to do that in the Advanced Editor, not in the user interface.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks lbendlin  for your quick feedback. But my problem remains the same. May I was not clear about my problem before. What I want is that to show data in row format . For ex: 2020,2021,2

       

      For a moment the data is in column format, but I need it to show like this where every Month, Year represents each column. I tried to do the same in Matrix view but there I am facing another problem. Whenever I am scrolling down, it not showing me the data.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

         

        So basically what I am looking for is something like this. This is in matrix viz. But I think what my customer is looking for is to show in the way where after 2020 it will show the only total of 2020 month values and then for 2021, they want to add other columns like JAN 2021, Feb 2021, ..December 2021 and then the total of values. I don't know how it possible, do you have any idea?