Forum Discussion

Radinooo's avatar
Radinooo
Regular Visitor
7 years ago
Solved

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...
  • PattemManohar's avatar
    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")

     

  • Radinooo's avatar
    Radinooo
    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