Visualizing Data values between dates

[spider_Monkey]

Hello Friends,

I am trying to build out a visulization that will show me how many reports (Reports Goal) between the time periods below.

So one example would be between 1/2-1/15 there would be 172 reports that were due. What I would like to do is have a line chart that shows the ebs and flows of what the goals are over time. 

So almost like a Gantt Chart but not ha! 

Person	Start	End	Reports Completed	Reports Goal
Macgyver	1/1/2022	1/15/2022	110	100
Johnson	1/2/2022	1/15/2022	62	80
Johnson	1/4/2022	2/1/2022	65	65
Grace	1/15/2022	2/4/2022	80	110
Sosa	5/3/2022	5/30/2022	100	125
Sosa	4/6/2022	5/1/2022	100	100
Grace	4/1/2022	5/18/2022	65	65
Macgyver	3/4/2022	3/15/2022	12	23

[spider_Monkey]

So I built a new table outside of query editor based off data from anohter table. 

[lbendlin]

As long as you included the index column there you are good. Doesn't matter where in the ETL process you do it.

[spider_Monkey]

Okay, so I am using union to unpivot this and I should be able to get there. Do you happen to have a screen shot of the visualizations pane from the graph you provided? 

[lbendlin]

Column = [Person] & [Index]

[spider_Monkey]

Thanks @lbendlin I'll play with adding this in, I appreciate your help! 

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDNCsMgDAfwd8m5YIwf+AaDwU47igcpZTtVaGGwt5/a2kjXS4j4U//Re3jE8fX9TAsMIIUUhERba45eYqmIEAYP9/Se1zRXQ1fcluL+sW6A+BlrtlLsbYnjdLqL+JTDPUqxz7TGvDRCte3c4pEGqyXTWy0sW3mi+2wtgmaQrbtK232b4pCq/7Y6gIIQfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Start = _t, End = _t, #"Reports Completed" = _t, #"Reports Goal" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Person", "Reports Completed", "Reports Goal", "Index"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Reports Completed", Int64.Type}, {"Reports Goal", Int64.Type}})
in
    #"Changed Type1"

You have multiple intervals per person, so I had to add an index column.

[spider_Monkey]

@lbendlin Can you send a screen shot of the table view to see how it looks after the edit to the query editor?

[lbendlin · Accepted solution]

find the pbix attached

[spider_Monkey]

@lbendlin Alright so I added an indexed column, but this might be a dumb question...This is off a table that I build so I can't see it in the query editor.

[lbendlin]

Not sure what you mean.  Unless you connect to a dataset or dataflows you should be able to see the query in the query editor.