cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grooble
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"

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
v-piga-msft Resident Rockstar
Resident Rockstar

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

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"

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors