How do i format my data to create a column chart?

[odor]

Hi i Have the following data:

Im trying to present this for each date, but i struggle to find a visual, and how to select values for axis, legend and values.

What i want i a chart that:

x-axis: Each "ConnectionX" should have its own column

y-axis: Should show the value of the ConnectionX

There is already a slicer that sorts out the dates in the Dashboard, seems to be so easy like "Column chart" here:

https://www.pluralsight.com/guides/bar-and-column-charts-in-power-bi

But i cant seem to place the values in the correct boxes (axis/legend/value etc)

[PaulDBrown]

You need to unpivot all the Connection columns 

[odor]

Hi again, so i test this.

My connection-columns, can range from 0-169, which means for one date i could get up to 169 duplicates. 
Any other way, could i create a new table with the connections, and have a 1-1-realationShip back to the date?

[PaulDBrown · Accepted solution]

Here is how you can do it. First of all, unpivot the connection columns in Power Query to get:

 Next create a Calendar Table and a Dimension table for Conecctions:

Create the relationships between the corresponding fields in the model

Create the measures for the visuals:

Sum Time = 
SUM('Table'[Time])

Since you can't use a Time format in a bar chart, convert this sum into seconds 

Time in seconds = 
HOUR([Sum Time]) * 3600 + MINUTE([Sum Time]) * 60 + SECOND([Sum Time])

Now create the visual using the date field from the calendar table (make the axis categorical), the connection field from the dimension table for the legend and  the measure(s). In the bar chart you can add the [Sum Time] as a tooltip.

I've attached the sample PBIX file

[PaulDBrown]

Can you provide a sample of the data?

[odor]

Hm, maybe its a bad example, but does it mean that all values must be unique:
https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

And will the data still be related to the actual date?