Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
For a social media report, I have a visual showing the average percentage viewers retained per second concerning video posts.
Example:
Seconds Video 1 Video 2 Video 3 Average Percentage Retained
0 0.95 0.95 0.95 0.95
1 0.90 0.93 0.87 0.91
2 0.85 0.90 0.80 0.85
The decimals basically mean that 90%, of the viewers watched 1 second of video 1. I created a calculated column called average percentage retained using the formula: (Average Percentage Video watched per Second = ([Video 1] + [Video 2] + [Video 3])/3).
Since I have to keep changing this every time I make a new video post, I'm looking for some alternatives that work without having to do this.
Solved! Go to Solution.
I am not able to run tranformation since data imported from excel and its available at your desktop but based on understanding you can copy paste below code at advance editor:-
let
Bron = Excel.Workbook(File.Contents("C:\Users\Dylan\Desktop\Data\Facebook post\Percentage of views per second.xlsx"), null, true),
#"Percentage of Views per second_Sheet" = Bron{[Item="Percentage of Views per second",Kind="Sheet"]}[Data],
#"Headers met verhoogd niveau" = Table.PromoteHeaders(#"Percentage of Views per second_Sheet", [PromoteAllScalars=true]),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Seconds", Int64.Type}, {"109362120541400_342171220593821", type number}, {"109362120541400_341586157318994", type number}, {"109362120541400_281096060034671", type number}, {"109362120541400_295363821941228", type number}, {"109362120541400_330793401731603", type number}, {"109362120541400_305269554283988", type number}, {"109362120541400_313523823458561", type number}, {"109362120541400_296695761808034", type number}, {"109362120541400_325923882218555", type number}, {"109362120541400_284139049730372", type number}, {"109362120541400_307816697362607", type number}, {"109362120541400_311663670311243", type number}, {"109362120541400_302894911188119", type number}, {"109362120541400_298575068286770", type number}, {"109362120541400_307153587428918", type number}}),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Duration", each #duration(0,0,0,[Seconds])),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Duration"}),
#"Renamed Columns" = Table.RenameColumns(#"Aangepaste kolom toegevoegd",{{"109362120541400_342171220593821", "Video 1"}, {"109362120541400_341586157318994", "Video 2"}, {"109362120541400_281096060034671", "Video 3"}, {"109362120541400_295363821941228", "Video 4"}, {"109362120541400_330793401731603", "Video 5"}, {"109362120541400_305269554283988", "Video 6"}, {"109362120541400_313523823458561", "Video 7"}, {"109362120541400_296695761808034", "Video 8"}, {"109362120541400_325923882218555", "Video 9"}, {"109362120541400_284139049730372", "Video 10"}, {"109362120541400_307816697362607", "Video 11"}, {"109362120541400_302894911188119", "Video 13"}, {"109362120541400_311663670311243", "Video 12"}, {"109362120541400_298575068286770", "Video 14"}, {"109362120541400_307153587428918", "Video 15"}}),
#"Added Custom" = Table.AddColumn
(
#"Renamed Columns",
"AverageMonth",
(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Renamed Columns"), each Text.Contains(_,"Video")))))
)
in
#"Added Custom"
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hello Samarth,
Thank you for your answering my question. However, I have to apologize since I didn't explain it clearly enough.
It's about the formula: (Average Percentage Video watched per Second = ([Video 1] + [Video 2] + [Video 3])/3).
If I add another video, this means I would have to manually change the formula to (Average Percentage Video watched per Second = ([Video 1] + [Video 2] + [Video 3]+[Video 4])/4).
This is rather tiresome so I was wondering if it was possible to have it done automatically with a correct formula?
@dillennwin Ok got it. You can add a custom column in Power Query with below code. It will take avg of all the column which contains "Video" in name:-
(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_,"Video")))))
Ouput:-
Now click on Close & Apply and change newly created column as percentage type.
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
This is indeed what I'm looking for, thank you very much. However, this is the output that I'm getting:
This is what I used:
each (row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"FB Percentage of Views per second"), each Text.Contains(_,"Video"))))))
The only change I made was in List.Select, to make it refer to how my table is called. Maybe more things need to be modified to get the desired result?
You dont need to make any changes. You can use it as it is:-
(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_,"Video")))))
if it is still not working then you can go to advance editor and replace last two line with below code:-
#"Added Custom" = Table.AddColumn
(
#"Changed Type",
"AverageMonth",
(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_,"Video")))))
)
in
#"Added Custom"
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
I initially tried doing this, however it gave me this error: Expression.Error: The name 'Changed Type' wasn't recognized. Make sure it's spelled correctly.
This is also the reason I changed it, thinking that would solve it. Replacing the last two lines in the advanced editor didn't seem to make a difference.
Could you please share your advance editor code?or your PBIX file after removing sensitive data?
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @dillennwin ,
You can directly change your avg column to percentage column from the top ribbon.
or you can create a column with below code:-
Column =
var _avg = DIVIDE(('Avg'[Video 1]+'Avg'[Video 2]+'Avg'[Video 3]),3)
return FORMAT(_avg,"0.00%")
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
I am not able to run tranformation since data imported from excel and its available at your desktop but based on understanding you can copy paste below code at advance editor:-
let
Bron = Excel.Workbook(File.Contents("C:\Users\Dylan\Desktop\Data\Facebook post\Percentage of views per second.xlsx"), null, true),
#"Percentage of Views per second_Sheet" = Bron{[Item="Percentage of Views per second",Kind="Sheet"]}[Data],
#"Headers met verhoogd niveau" = Table.PromoteHeaders(#"Percentage of Views per second_Sheet", [PromoteAllScalars=true]),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Seconds", Int64.Type}, {"109362120541400_342171220593821", type number}, {"109362120541400_341586157318994", type number}, {"109362120541400_281096060034671", type number}, {"109362120541400_295363821941228", type number}, {"109362120541400_330793401731603", type number}, {"109362120541400_305269554283988", type number}, {"109362120541400_313523823458561", type number}, {"109362120541400_296695761808034", type number}, {"109362120541400_325923882218555", type number}, {"109362120541400_284139049730372", type number}, {"109362120541400_307816697362607", type number}, {"109362120541400_311663670311243", type number}, {"109362120541400_302894911188119", type number}, {"109362120541400_298575068286770", type number}, {"109362120541400_307153587428918", type number}}),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Duration", each #duration(0,0,0,[Seconds])),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Duration"}),
#"Renamed Columns" = Table.RenameColumns(#"Aangepaste kolom toegevoegd",{{"109362120541400_342171220593821", "Video 1"}, {"109362120541400_341586157318994", "Video 2"}, {"109362120541400_281096060034671", "Video 3"}, {"109362120541400_295363821941228", "Video 4"}, {"109362120541400_330793401731603", "Video 5"}, {"109362120541400_305269554283988", "Video 6"}, {"109362120541400_313523823458561", "Video 7"}, {"109362120541400_296695761808034", "Video 8"}, {"109362120541400_325923882218555", "Video 9"}, {"109362120541400_284139049730372", "Video 10"}, {"109362120541400_307816697362607", "Video 11"}, {"109362120541400_302894911188119", "Video 13"}, {"109362120541400_311663670311243", "Video 12"}, {"109362120541400_298575068286770", "Video 14"}, {"109362120541400_307153587428918", "Video 15"}}),
#"Added Custom" = Table.AddColumn
(
#"Renamed Columns",
"AverageMonth",
(row)=> List.Average(Record.FieldValues(Record.SelectFields(row, List.Select(Table.ColumnNames(#"Renamed Columns"), each Text.Contains(_,"Video")))))
)
in
#"Added Custom"
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Thank you very much, I appreciate you for helping me for the past few days.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |