Rows transformation to columns

[Anonymous]

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 ?

[lbendlin]

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

[Anonymous]

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]

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?

[Icey · Accepted solution]

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.

[Icey]

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]

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]

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?