Forum Discussion
Plot calculated columns
Hi , ivial
Here are the steps you can refer to :
(1)This is my test data :
(2)We need to unpivot your [m1]..[m24] columns in Power Query Editor, the m language is like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZO7DoJQEAX/hZqCu/f9LYTCGEvUwsa/N8CykZw9heyEM9PhPA/31/q+Pb9hGIftJ879PG5rGJbRZNEhOhfkqENyLshJh+zcTZZ/OetQnAty0aE6F+SqQ3MuyE2H7lyQuw5hcgH8/e3xCISwCSYJIWzEpEgIm2hSIrQ18dIkkzIhbLJJhRA2xaRKCD6+Y98fjRA2zaROCJt+TjIRwr/cdE4SCGETTBJC2IhJkRA20aRECJtkUiaETTapEMKmmFQJYVNNaoS2Jl2aZlInhE0/pzgRgmZ/ezwCIW2WHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Proyecto = _t, m1 = _t, m2 = _t, m3 = _t, m4 = _t, m5 = _t, m6 = _t, #"Uni Subproyecto" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Proyecto", type text}, {"m1", Int64.Type}, {"m2", Int64.Type}, {"m3", Int64.Type}, {"m4", Int64.Type}, {"m5", Int64.Type}, {"m6", Int64.Type}, {"Uni Subproyecto", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Proyecto", "Uni Subproyecto"}, "Month", "Value") in #"Unpivoted Columns"The result is as follows:
(3)We can create a calculated column in Power Bi Desktop in our table to get the "Month_Number":
Month_Number = RIGHT('Table'[Month],1)(4)Then we just need to put the filed we need on the visual and we will meet your need :
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, ivial
You can also unpivot the table using dax , like this:
[9:04 AM] AniyaZhang Table 2 = UNION( SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 1 , "m_value" , [m1]) , SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 2 , "m_value" , [m2]))For more information, you can refer to :
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , ivial
Here are the steps you can refer to :
(1)This is my test data :
(2)We need to unpivot your [m1]..[m24] columns in Power Query Editor, the m language is like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZO7DoJQEAX/hZqCu/f9LYTCGEvUwsa/N8CykZw9heyEM9PhPA/31/q+Pb9hGIftJ879PG5rGJbRZNEhOhfkqENyLshJh+zcTZZ/OetQnAty0aE6F+SqQ3MuyE2H7lyQuw5hcgH8/e3xCISwCSYJIWzEpEgIm2hSIrQ18dIkkzIhbLJJhRA2xaRKCD6+Y98fjRA2zaROCJt+TjIRwr/cdE4SCGETTBJC2IhJkRA20aRECJtkUiaETTapEMKmmFQJYVNNaoS2Jl2aZlInhE0/pzgRgmZ/ezwCIW2WHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Proyecto = _t, m1 = _t, m2 = _t, m3 = _t, m4 = _t, m5 = _t, m6 = _t, #"Uni Subproyecto" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Proyecto", type text}, {"m1", Int64.Type}, {"m2", Int64.Type}, {"m3", Int64.Type}, {"m4", Int64.Type}, {"m5", Int64.Type}, {"m6", Int64.Type}, {"Uni Subproyecto", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Proyecto", "Uni Subproyecto"}, "Month", "Value")
in
#"Unpivoted Columns"The result is as follows:
(3)We can create a calculated column in Power Bi Desktop in our table to get the "Month_Number":
Month_Number = RIGHT('Table'[Month],1)(4)Then we just need to put the filed we need on the visual and we will meet your need :
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you very much for your reply. Nevertheless, the problem I have is that I don't have columns m1 to m24 in the Power Query Editor since they are calculated columns and they don't appear in the editor (or maybe I don't know how to do it).
So, how could I manage or ("unpivot") these calculated columns? I share the link with my table data.
Appreciate if you someone could clarify how could I plot:
x-values: 1, 2, 3, 4, .... 24
y-values: sum of IES in m1, sum of IES in m2, sum of IES in m3, sum of IES in m4, ...., sum of IES in m24
the link is the following: https://sirvesa-my.sharepoint.com/:f:/g/personal/ivial_sirve_cl/EpxokCAuhodBidtSZl2qRkQB11TdxdC5r0xW1iZxQahTfg?e=lL0sW2
Thanks a lot!
Greetings,
Hi, ivial
You can also unpivot the table using dax , like this:
[9:04 AM] AniyaZhang Table 2 = UNION( SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 1 , "m_value" , [m1]) , SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 2 , "m_value" , [m2]))For more information, you can refer to :
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly