multiple dates in columns for column chart

[Radinooo]

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

[PattemManohar · Accepted 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")

[PattemManohar · Accepted 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")

[Radinooo · Accepted solution]

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]

@Radinooo That's good !! Happy Learning !!