Forum Discussion
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.2018 | Prj1 |
| part2 | 25.6.2018 | 15.7.2018 | 21.9.2018 | Prj1 |
| Part3 | 25.6.2018 | 15.7.2018 | Prj2 | |
| Part4 | 25.6.2018 | 11.8.2018 | 21.9.2018 | Prj2 |
| Part5 | 25.6.2018 | 14.7.2018 | Prj2 | |
| Part6 | 15.7.2018 | Prj1 | ||
| Part7 | 15.7.2018 | 11.8.2018 | Prj2 | |
| Part8 | 15.7.2018 | 11.8.2018 | Prj1 | |
| Part9 | 15.7.2018 | Prj1 | ||
| Part10 | 15.7.2018 | Prj1 | ||
| Part11 | 15.7.2018 | Prj2 | ||
| Part12 | 11.8.2018 | 21.9.2018 | Prj1 | |
| Part13 | 11.8.2018 | 21.9.2018 | Prj1 | |
| Part14 | 11.8.2018 | 21.9.2018 | Prj2 | |
| Part15 | 11.8.2018 | 21.9.2018 | Prj2 | |
| Part16 | 11.8.2018 | 21.9.2018 | Prj1 | |
| Part17 | 11.8.2018 | 21.9.2018 | Prj1 | |
| Part18 | 11.8.2018 | 21.9.2018 | Prj1 | |
| Part19 | 11.8.2018 | 21.9.2018 | Prj2 | |
| Part20 | 11.8.2018 | 21.9.2018 | Prj2 |
each unique part (in column "Parts") has 3columns for measurements dates, they will be filled continuosly.
Parts are separated to two project "prj1" and "prj2" (column "Project")
Now I want to visualise daily summary of measuremets for prj1 and prj2.
Is there way to show in one chart how many measurements for prj1 and prj2 was done for each day ?
So from previous image chart will shows:
data for chart should be :
| Date | prj1 | prj2 | total |
| 25.6.2018 | 2 | 3 | 5 |
| 14.7.2018 | 1 | 1 | 2 |
| 15.7.2018 | 5 | 3 | 8 |
| 11.8.2018 | 6 | 6 | 12 |
| 21.9.2018 | 7 | 5 | 12 |
Thank you
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
3 Replies
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
Radinooo That's good !! Happy Learning !!