cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
grooble Frequent Visitor
Frequent Visitor

Convert Facebook Insights rows of list to column

I'm getting a JSON data set from the Facebook Graph API with the intention of creating some Power BI reports.

 

Here is the request:
https://graph.facebook.com/v3.3/[MY_PAGE]/posts?fields=id,message,created_time,attachments{media_typ...), shares.limit(0).summary(true),insights.metric(post_impressions_unique,post_impressions_fan_unique,post_reactions_by_type_total,post_clicks_by_type_unique,post_video_avg_time_watched,post_video_views_10s_unique)&access_token=[MY_PAGE_TOKEN]"

 

In the Facebook Graph API Explorer, I can see that I have all the information I need.

In Power BI, it is all going well until I get to Insights. Here, the usual expand operation creates separate rows for each of the different insights values. What I want is separate columns for each insight value, so that one row holds a single post record.
I looked at this answer to convert rows to columns:

https://community.powerbi.com/t5/Desktop/How-to-convert-a-Column-that-contains-List-Value-and-inside...

 

It seems to work. If I select the "name" of the insight field, then it populates new colums with the name of the value for each row.

But if I select values, then I get an error saying I can't convert from List to text.

Here is the Power BI query:

 

        let
        MyJsonRecord = Json.Document(Web.Contents("https://graph.facebook.com/v3.3/[MY_PAGE]/posts?fields=id,message,created_time,attachments{media_type},likes.limit(0).summary(true),comments.limit(0).summary(true), shares.limit(0).summary(true),insights.metric(post_impressions_unique,post_impressions_fan_unique,post_reactions_by_type_total,post_clicks_by_type_unique,post_video_avg_time_watched,post_video_views_10s_unique)&access_token=[MY_PAGE_TOKEN]")),
MyJsonTable = Table.FromRecords( { MyJsonRecord } ),
            data = MyJsonTable{0}[data],
            #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "message", "created_time", "attachments", "likes", "comments", "shares", "insights"}, {"Column1.id", "Column1.message", "Column1.created_time", "Column1.attachments", "Column1.likes", "Column1.comments", "Column1.shares", "Column1.insights"}),
            #"Expanded {0}1" = Table.ExpandRecordColumn(#"Expanded {0}", "Column1.insights", {"data"}, {"Column1.insights.data"}),
            #"Converted to Column" = Table.TransformColumns(#"Expanded {0}1" , {{"Column1.insights.data", each Table.Transpose(Table.FromList(List.Transform(_, each Record.Field(_, "values"))))}})
        in
            #"Converted to Column"

I feel like I'm almost there.

Is there any way to get this working?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Convert Facebook Insights rows of list to column

Please try this code:

 

let
    Source = <YourJSON>
    #"MyJsonRecord" = Json.Document(Source),
    data = MyJsonRecord[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "message", "created_time", "attachments", "likes", "comments", "shares", "insights"}, {"id", "message", "created_time", "attachments", "likes", "comments", "shares", "insights"}),
    #"Expanded insights1" = Table.ExpandRecordColumn(#"Expanded Column1", "insights", {"data"}, {"data"}),
    #"Added Custom" = Table.AddColumn(#"Expanded insights1", "Custom", each Table.FromColumns(List.Transform([data], (x) => x[values]), List.Transform([data], (x) => x[name]))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"post_video_avg_time_watched", "post_video_views_10s_unique", "post_impressions_unique", "post_impressions_fan_unique", "post_reactions_by_type_total", "post_clicks_by_type_unique"}, {"post_video_avg_time_watched", "post_video_views_10s_unique", "post_impressions_unique", "post_impressions_fan_unique", "post_reactions_by_type_total", "post_clicks_by_type_unique"}),
    #"Expanded post_video_avg_time_watched" = Table.ExpandRecordColumn(#"Expanded Custom", "post_video_avg_time_watched", {"value"}, {"post_video_avg_time_watched.value"}),
    #"Expanded post_video_views_10s_unique" = Table.ExpandRecordColumn(#"Expanded post_video_avg_time_watched", "post_video_views_10s_unique", {"value"}, {"post_video_views_10s_unique.value"}),
    #"Expanded post_impressions_unique" = Table.ExpandRecordColumn(#"Expanded post_video_views_10s_unique", "post_impressions_unique", {"value"}, {"post_impressions_unique.value"}),
    #"Expanded post_impressions_fan_unique" = Table.ExpandRecordColumn(#"Expanded post_impressions_unique", "post_impressions_fan_unique", {"value"}, {"post_impressions_fan_unique.value"}),
    #"Expanded post_reactions_by_type_total" = Table.ExpandRecordColumn(#"Expanded post_impressions_fan_unique", "post_reactions_by_type_total", {"value"}, {"post_reactions_by_type_total.value"}),
    #"Expanded post_reactions_by_type_total.value" = Table.ExpandRecordColumn(#"Expanded post_reactions_by_type_total", "post_reactions_by_type_total.value", {"like", "love", "wow"}, {"post_reactions_by_type_total.value.like", "post_reactions_by_type_total.value.love", "post_reactions_by_type_total.value.wow"}),
    #"Expanded post_clicks_by_type_unique" = Table.ExpandRecordColumn(#"Expanded post_reactions_by_type_total.value", "post_clicks_by_type_unique", {"value"}, {"post_clicks_by_type_unique.value"}),
    #"Expanded post_clicks_by_type_unique.value" = Table.ExpandRecordColumn(#"Expanded post_clicks_by_type_unique", "post_clicks_by_type_unique.value", {"other clicks", "photo view", "link clicks"}, {"post_clicks_by_type_unique.value.other clicks", "post_clicks_by_type_unique.value.photo view", "post_clicks_by_type_unique.value.link clicks"})
in
    #"Expanded post_clicks_by_type_unique.value"

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Convert Facebook Insights rows of list to column

Hi @grooble ,

More details will much helpful for me to do further research.

If it is convenient, could you share some data sample which could reproduce your scenario and your desired output so that I could made the similar data model to have a test?

In addition, maybe @ImkeF  will have some ideas for your error.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
grooble Frequent Visitor
Frequent Visitor

Re: Convert Facebook Insights rows of list to column

Here is an example of the json data that I'm parseing with PowerBI.

 

In any case, I may have made some progress by changing the Converted to Column line as follows.

#"Converted to Column" = Table.TransformColumns(#"Expanded {0}1" , {{"Column1.insights.data", each Table.Transpose(Table.FromList(List.Transform(_, each Record.Field(_, "values"))))}})

to

#"Converted to Column" = Table.TransformColumns(#"Expanded {0}1" , {{"Column1.insights.data", each Table.Transpose(Table.FromList(List.Transform(_, each Text.Format("#[result]",[result=Record.Field(List.First(Record.Field(_, "values")), "value")]))))}}),

This is a partial solution, but there are still 2 "record" columns that I can't yet access.

Super User III
Super User III

Re: Convert Facebook Insights rows of list to column

Please try this code:

 

let
    Source = <YourJSON>
    #"MyJsonRecord" = Json.Document(Source),
    data = MyJsonRecord[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "message", "created_time", "attachments", "likes", "comments", "shares", "insights"}, {"id", "message", "created_time", "attachments", "likes", "comments", "shares", "insights"}),
    #"Expanded insights1" = Table.ExpandRecordColumn(#"Expanded Column1", "insights", {"data"}, {"data"}),
    #"Added Custom" = Table.AddColumn(#"Expanded insights1", "Custom", each Table.FromColumns(List.Transform([data], (x) => x[values]), List.Transform([data], (x) => x[name]))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"post_video_avg_time_watched", "post_video_views_10s_unique", "post_impressions_unique", "post_impressions_fan_unique", "post_reactions_by_type_total", "post_clicks_by_type_unique"}, {"post_video_avg_time_watched", "post_video_views_10s_unique", "post_impressions_unique", "post_impressions_fan_unique", "post_reactions_by_type_total", "post_clicks_by_type_unique"}),
    #"Expanded post_video_avg_time_watched" = Table.ExpandRecordColumn(#"Expanded Custom", "post_video_avg_time_watched", {"value"}, {"post_video_avg_time_watched.value"}),
    #"Expanded post_video_views_10s_unique" = Table.ExpandRecordColumn(#"Expanded post_video_avg_time_watched", "post_video_views_10s_unique", {"value"}, {"post_video_views_10s_unique.value"}),
    #"Expanded post_impressions_unique" = Table.ExpandRecordColumn(#"Expanded post_video_views_10s_unique", "post_impressions_unique", {"value"}, {"post_impressions_unique.value"}),
    #"Expanded post_impressions_fan_unique" = Table.ExpandRecordColumn(#"Expanded post_impressions_unique", "post_impressions_fan_unique", {"value"}, {"post_impressions_fan_unique.value"}),
    #"Expanded post_reactions_by_type_total" = Table.ExpandRecordColumn(#"Expanded post_impressions_fan_unique", "post_reactions_by_type_total", {"value"}, {"post_reactions_by_type_total.value"}),
    #"Expanded post_reactions_by_type_total.value" = Table.ExpandRecordColumn(#"Expanded post_reactions_by_type_total", "post_reactions_by_type_total.value", {"like", "love", "wow"}, {"post_reactions_by_type_total.value.like", "post_reactions_by_type_total.value.love", "post_reactions_by_type_total.value.wow"}),
    #"Expanded post_clicks_by_type_unique" = Table.ExpandRecordColumn(#"Expanded post_reactions_by_type_total.value", "post_clicks_by_type_unique", {"value"}, {"post_clicks_by_type_unique.value"}),
    #"Expanded post_clicks_by_type_unique.value" = Table.ExpandRecordColumn(#"Expanded post_clicks_by_type_unique", "post_clicks_by_type_unique.value", {"other clicks", "photo view", "link clicks"}, {"post_clicks_by_type_unique.value.other clicks", "post_clicks_by_type_unique.value.photo view", "post_clicks_by_type_unique.value.link clicks"})
in
    #"Expanded post_clicks_by_type_unique.value"

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors