Forum Discussion
multiple dates in columns for column chart
- 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 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")
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
- PattemManohar7 years agoCommunity Champion
Radinooo That's good !! Happy Learning !!