Convert Facebook Insights rows of list to column

[grooble]

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?

[v-piga-msft]

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

[grooble]

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.

[ImkeF · Accepted solution]

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"