Forum Discussion
Radinooo
7 years agoRegular Visitor
multiple dates in columns for column chart
Hello dear all, I need help regarding creating column chart for yearly review of parts controls. My table seems like this: Parts 1st 2nd 3th project Part1 25.6.2018 14.7.2018 21.9.201...
- 7 years ago
Radinooo Please transform the data as required for the chart in "Power Query Editor", here is the steps I've followed:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZK7DoMwDEV/BXlGVmzyIH/BHjF07YQQ/69SVTTIgRBvHs71PY6SEkyvdSPogR16ZEPjPpPFcMxMGI95Wt8Ec59g2UMsQ+4h9G0aKqHuB/MfthImHG8acshVbika/IVCJ52DgM4axUZ51AWcN8eWejJNFN1T2Y648ozlzkFF22f6ZOJUtFeZBBVd+1olHTXebJro+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Parts = _t, #"1st" = _t, #"2nd" = _t, #"3th" = _t, project = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parts", type text}, {"1st", type date}, {"2nd", type date}, {"3th", type date}, {"project", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"1st", "2nd", "3th"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Only Selected Columns", {"Value", "project"}, {{"Count", each Table.RowCount(_), type number}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Value", Order.Ascending}}), #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[project]), "project", "Count"), #"Inserted Addition" = Table.AddColumn(#"Pivoted Column", "Addition", each [Prj1] + [Prj2], type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Addition",{{"Addition", "Total"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", type date}, {"Prj1", Int64.Type}, {"Prj2", Int64.Type}, {"Total", Int64.Type}}) in #"Changed Type1"The output will be as below :
Now, we can create the Chart visual as below (Please note, the "Type" property for x-axis need to be changed as "Categorical")
- 7 years ago
Hello,
Thank you for prompt action.
I forgot to say I'm beginner in Power BI (only about 5days of exp.)
But I already successfully modiefied Query settings as you suggested.
It works for me.
Thank you
best regards
Rado
Radinooo
7 years agoRegular Visitor
Hello,
Thank you for prompt action.
I forgot to say I'm beginner in Power BI (only about 5days of exp.)
But I already successfully modiefied Query settings as you suggested.
It works for me.
Thank you
best regards
Rado
PattemManohar
7 years agoCommunity Champion
Radinooo That's good !! Happy Learning !!