Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
fredtusveld
Regular Visitor

Audio quality timeseries

Hi,

i want to have a graph depicting audio quality over time. I can do this for 1 hour intervals as an example. But the query then only returns streams that started and ended within that interval, which leaves out a significant number. 

Is there a way to do this?

 

thanks,

Fred

1 ACCEPTED SOLUTION

Yes, I used this myself to record/analyse phonerecords. If for example you add a column with a sound quality score this score would be added to every row. You can then easily aggregate per minute what the average/max/min sound quality was per minute over all the streams that where active in that specific minute. 

 

I also experimented with this in seconds. That's also possible with a little code adjustment, but this will generate a huge amount of rows. 

 

Hope you get to a workable solution!

View solution in original post

6 REPLIES 6
fredtusveld
Regular Visitor

I thought a bit more about this and now. So if i understand this correctly, this technique would allow me generate new rows/records from a large time interval to smaller ones so that i could then aggregate the measurements for all rows active in a minute interval and by doing so create averages per interval for any measurements that are recorded in the original -before adding new - rows/records. This should work if in the original list i have all streams or calls, with call quality. I will try this. 

Yes, I used this myself to record/analyse phonerecords. If for example you add a column with a sound quality score this score would be added to every row. You can then easily aggregate per minute what the average/max/min sound quality was per minute over all the streams that where active in that specific minute. 

 

I also experimented with this in seconds. That's also possible with a little code adjustment, but this will generate a huge amount of rows. 

 

Hope you get to a workable solution!

thanks again! I'm curious into your dashboard - i did not really find sound quality metrics.

In my work we use multiple tools to analyze app performance, mostly more on the network path. If you follow me on X /Twitter we could have a chat

Grateful for your effort Chiel, great solution! 

fredtusveld
Regular Visitor

thanks a lot Chiel. THis looks very useful. M-code is new to me and i have to adapt this for CQD. I will report back. 

ChielFaber
Super User
Super User

You can create a snapshot type table in PowerQuery. The prequisite for this is that you have a column with and start datetime and a column with an end datetime.

 

If you have the two columns you can add rows for each second, minute, hour every stream lasts. Be carefull because if you chose second you will add a lot of rows to your table!

 

First create a column calculating the minutes between the starttime and the endtime. Format this column as a whole number.

Start.PNG

Then adjust this M-code to your specific needs

 

#"New Column" = Table.AddColumn(#"Name of your previous Step", "NewColumnName", each List.DateTimes([Starttime], Duration.Hours([Endtime] - [Starttime]) + [minutes_between] , #duration(0,0,1,0)))

 

 

Name of your previous Step = Replace this with the previous step name in the Advanced editor

NewColumnName = The name for the new column. Adjust this to your own desire

Starttime = column with the starting datetime

Endtime = column with the enddatetime

minutes_between = the calculated column with the amount of minutes between the times.

 

This will give you a new column containing a list.

 

List newcolumn.PNG

 

When you expand the list it will create new rows for every minute the stream was active. You've just created a snapshot table in PowerQuery.

 

NewRows.PNG

Hope this helps. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors