Forum Discussion
How do i format my data to create a column chart?
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
You need to unpivot all the Connection columns
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?
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
Can you provide a sample of the data?