Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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")
Proud to be a PBI Community Champion
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")
Proud to be a PBI Community Champion
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 !!
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |