Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Can someone tell me how can I do that in Power BI ?
Solved! Go to 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.
You need to do that in the Advanced Editor, not in the user interface.
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.
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?
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.
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.
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
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?