Get multiple json files from Onedrive folder into powerbi

[Anonymous]

Hello All,

In my Onedrive i am having more than 1000 json files and i would like to load those json files into powerbi and create a report.

I have tried  with a single json file in below images.

M query:-

let
    Source = Json.Document(File.Contents("C:\Users\mohanv1\OneDrive\Tweets\246989832614898.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Then the table will be as i need, as in below image.

As i mentioned i have more than 1000 files in my onedrive folder.

So i tried using folder menthod in get data.

But i am unable to convert those, as i did to the above single json file.

I have loaded all json files using folder and then as in below image,

Then, as Content column contians the actual info, i have made as new query and changed it to table from list.

Then still it is having binary content, so i tried to expand the rows but here it changed as below image.

So, like i did as above for single json, here also i tried to transpose the table but it given me error as it is too complex to display.

I tried many other ways but at last it came here itself.

i cant attach the sample json files so i am jus copying twi three json data here.

{"created_at":"Wed Jun 07 07:37:00 +0000 2017","id":872356704708591618,"id_str":"872356704708591618","
text":"Turn your next #outstation #business trip into something much more than just meetings and clients. #BusinessTravel\u2026 https:\/\/t.co\/fYZ7lM33gm","display_text_range":[0,140],"source":"\u003ca href=\"https:\/\/about.twitter.com\/products\/tweetdeck\" rel=\"nofollow\"\u003eTweetDeck\u003c\/a\u003e","truncated":true,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":425641294,"id_str":"425641294","name":"Carzonrent","screen_name":"CarzonrentIN","location":null,"url":"https:\/\/www.facebook.com\/carzonrent","description":"Carzonrent (India) Pvt. Ltd. is India\u2019s leading personal ground transportation service","protected":false,"verified":false,"followers_count":1123,"friends_count":115,"listed_count":42,"favourites_count":18,"statuses_count":2708,"created_at":"Thu Dec 01 07:39:23 +0000 2011","utc_offset":19800,"time_zone":"Chennai","geo_enabled":true,"lang":"en","contributors_enabled":false,"is_translator":false,"profile_background_color":"C0DEED","profile_background_image_url":"http:\/\/pbs.twimg.com\/profile_background_images\/438604701169233920\/x8iGNcl7.png","profile_background_image_url_https":"https:\/\/pbs.twimg.com\/profile_background_images\/438604701169233920\/x8iGNcl7.png","profile_background_tile":false,"profile_link_color":"0084B4","profile_sidebar_border_color":"FFFFFF","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http:\/\/pbs.twimg.com\/profile_images\/378800000311255075\/c75d2cdfc3683340e8085e9b21d6bd3f_normal.jpeg","profile_image_url_https":"https:\/\/pbs.twimg.com\/profile_images\/378800000311255075\/c75d2cdfc3683340e8085e9b21d6bd3f_normal.jpeg","profile_banner_url":"https:\/\/pbs.twimg.com\/profile_banners\/425641294\/1487239958","default_profile":false,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"extended_tweet":{"full_text":"Turn your next #outstation #business trip into something much more than just meetings and clients. #BusinessTravel #Travel #Corporate https:\/\/t.co\/tDx8TXFyT7","display_text_range":[0,133],"entities":{"hashtags":[{"text":"outstation","indices":[15,26]},{"text":"business","indices":[27,36]},{"text":"BusinessTravel","indices":[99,114]},{"text":"Travel","indices":[115,122]},{"text":"Corporate","indices":[123,133]}],"urls":[],"user_mentions":[],"symbols":[],"media":[{"id":871989316741758977,"id_str":"871989316741758977","indices":[134,157],"media_url":"http:\/\/pbs.twimg.com\/media\/DBntqeBUAAEiVD6.jpg","media_url_https":"https:\/\/pbs.twimg.com\/media\/DBntqeBUAAEiVD6.jpg","url":"https:\/\/t.co\/tDx8TXFyT7","display_url":"pic.twitter.com\/tDx8TXFyT7","expanded_url":"https:\/\/twitter.com\/CarzonrentIN\/status\/872356704708591618\/photo\/1","type":"photo","sizes":{"medium":{"w":1080,"h":1080,"resize":"fit"},"large":{"w":1080,"h":1080,"resize":"fit"},"thumb":{"w":150,"h":150,"resize":"crop"},"small":{"w":680,"h":680,"resize":"fit"}}}]},"extended_entities":{"media":[{"id":871989316741758977,"id_str":"871989316741758977","indices":[134,157],"media_url":"http:\/\/pbs.twimg.com\/media\/DBntqeBUAAEiVD6.jpg","media_url_https":"https:\/\/pbs.twimg.com\/media\/DBntqeBUAAEiVD6.jpg","url":"https:\/\/t.co\/tDx8TXFyT7","display_url":"pic.twitter.com\/tDx8TXFyT7","expanded_url":"https:\/\/twitter.com\/CarzonrentIN\/status\/872356704708591618\/photo\/1","type":"photo","sizes":{"medium":{"w":1080,"h":1080,"resize":"fit"},"large":{"w":1080,"h":1080,"resize":"fit"},"thumb":{"w":150,"h":150,"resize":"crop"},"small":{"w":680,"h":680,"resize":"fit"}}}]}},"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[{"text":"outstation","indices":[15,26]},{"text":"business","indices":[27,36]},{"text":"BusinessTravel","indices":[99,114]}],"urls":[{"url":"https:\/\/t.co\/fYZ7lM33gm","expanded_url":"https:\/\/twitter.com\/i\/web\/status\/872356704708591618","display_url":"twitter.com\/i\/web\/status\/8\u2026","indices":[116,139]}],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"possibly_sensitive":false,"filter_level":"low","lang":"en","timestamp_ms":"1496821020663"}

{"created_at":"Wed Jun 07 07:37:00 +0000 2017","id":872356704683532290,"id_str":"872356704683532290","text":"RT @iipum: \u0e23\u0e48\u0e32\u0e07\u0e01\u0e32\u0e22\u0e02\u0e2d\u0e07\u0e17\u0e47\u0e2d\u0e1b\u0e08\u0e30\u0e15\u0e2d\u0e1a\u0e2a\u0e19\u0e2d\u0e07\u0e15\u0e48\u0e2d\u0e01\u0e32\u0e23\u0e01\u0e23\u0e30\u0e15\u0e38\u0e19\u0e17\u0e35\u0e48\u0e41\u0e23\u0e07\u0e46\u0e40\u0e17\u0e48\u0e32\u0e19\u0e31\u0e49\u0e19 \u0e17\u0e32\u0e07\u0e04\u0e38\u0e13\u0e2b\u0e21\u0e2d\u0e08\u0e36\u0e07\u0e1e\u0e39\u0e14\u0e44\u0e14\u0e49\u0e27\u0e48\u0e32\u0e40\u0e02\u0e32\u0e22\u0e31\u0e07\u0e2d\u0e22\u0e39\u0e48\u0e43\u0e19\u0e2d\u0e32\u0e01\u0e32\u0e23\u0e17\u0e35\u0e48\u0e2b\u0e21\u0e14\u0e2a\u0e15\u0e34","source":"\u003ca href=\"http:\/\/twitter.com\/download\/iphone\" rel=\"nofollow\"\u003eTwitter for iPhone\u003c\/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":532988804,"id_str":"532988804","name":"\u0e16\u0e38\u0e07\u0e41\u0e1b\u0e49\u0e07\u0e42\u0e01\u0e30&\u0e17\u0e2d\u0e21\u0e42\u0e01\u0e30\ud83c\udf3c","screen_name":"tungpangkopp","location":null,"url":null,"description":"BIGBANG TOM WINNER+nam 2NE1 ... GDTOMMINOBOM\ud83d\udc9b \u0e40\u0e23\u0e32\u0e08\u0e30\u0e1b\u0e01\u0e1b\u0e49\u0e2d\u0e07\u0e19\u0e32\u0e22\u0e19\u0e49\u0e2d\u0e22\u0e40\u0e2d\u0e07\u0e19\u0e30\ud83d\udc9e #\u0e22\u0e32\u0e21\u0e25\u0e39\u0e01\u0e21\u0e35\u0e20\u0e31\u0e22\u0e43\u0e08\u0e01\u0e47\u0e40\u0e1b\u0e47\u0e19\u0e41\u0e21\u0e48\u0e22\u0e32\u0e21\u0e25\u0e39\u0e01\u0e2b\u0e25\u0e48\u0e2d\u0e41\u0e17\u0e49\u0e41\u0e21\u0e48\u0e02\u0e2d\u0e40\u0e1b\u0e47\u0e19\u0e40\u0e21\u0e35\u0e22 #\u0e27\u0e31\u0e19\u0e19\u0e35\u0e49\u0e40\u0e1b\u0e47\u0e19\u0e40\u0e21\u0e35\u0e22","protected":false,"verified":false,"followers_count":424,"friends_count":270,"listed_count":3,"favourites_count":15465,"statuses_count":94218,"created_at":"Thu Mar 22 09:48:28 +0000 2012","utc_offset":25200,"time_zone":"Bangkok","geo_enabled":true,"lang":"th","contributors_enabled":false,"is_translator":false,"profile_background_color":"DEBA09","profile_background_image_url":"http:\/\/pbs.twimg.com\/profile_background_images\/378800000064347683\/6e318be4fe17791cfc7f87b1863ad62b.jpeg","profile_background_image_url_https":"https:\/\/pbs.twimg.com\/profile_background_images\/378800000064347683\/6e318be4fe17791cfc7f87b1863ad62b.jpeg","profile_background_tile":true,"profile_link_color":"E81C4F","profile_sidebar_border_color":"FFFFFF","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http:\/\/pbs.twimg.com\/profile_images\/871701126667685889\/NEcIAbJK_normal.jpg","profile_image_url_https":"https:\/\/pbs.twimg.com\/profile_images\/871701126667685889\/NEcIAbJK_normal.jpg","profile_banner_url":"https:\/\/pbs.twimg.com\/profile_banners\/532988804\/1496630869","default_profile":false,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"retweeted_status":{"created_at":"Wed Jun 07 07:23:48 +0000 2017","id":872353382819876866,"id_str":"872353382819876866","text":"\u0e23\u0e48\u0e32\u0e07\u0e01\u0e32\u0e22\u0e02\u0e2d\u0e07\u0e17\u0e47\u0e2d\u0e1b\u0e08\u0e30\u0e15\u0e2d\u0e1a\u0e2a\u0e19\u0e2d\u0e07\u0e15\u0e48\u0e2d\u0e01\u0e32\u0e23\u0e01\u0e23\u0e30\u0e15\u0e38\u0e19\u0e17\u0e35\u0e48\u0e41\u0e23\u0e07\u0e46\u0e40\u0e17\u0e48\u0e32\u0e19\u0e31\u0e49\u0e19 \u0e17\u0e32\u0e07\u0e04\u0e38\u0e13\u0e2b\u0e21\u0e2d\u0e08\u0e36\u0e07\u0e1e\u0e39\u0e14\u0e44\u0e14\u0e49\u0e27\u0e48\u0e32\u0e40\u0e02\u0e32\u0e22\u0e31\u0e07\u0e2d\u0e22\u0e39\u0e48\u0e43\u0e19\u0e2d\u0e32\u0e01\u0e32\u0e23\u0e17\u0e35\u0e48\u0e2b\u0e21\u0e14\u0e2a\u0e15\u0e34","source":"\u003ca href=\"http:\/\/twitter.com\" rel=\"nofollow\"\u003eTwitter Web Client\u003c\/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":270380549,"id_str":"270380549","name":"\u0e0a\u0e2d\u0e1a\u0e1a\u0e48\u0e19 \ud83d\udc51","screen_name":"iipum","location":"Thailand \u0e14\u0e34\u0e19\u0e41\u0e14\u0e19\u0e2d\u0e32\u0e2b\u0e32\u0e23\u0e2d\u0e23\u0e48\u0e2d\u0e22 ","url":null,"description":"Twitter \u0e21\u0e35\u0e44\u0e27\u0e49\u0e1a\u0e48\u0e19!! \u0e17\u0e27\u0e34\u0e15\u0e17\u0e38\u0e01\u0e2a\u0e34\u0e48\u0e07\u0e17\u0e35\u0e48\u0e1e\u0e2d\u0e43\u0e08 \u0e17\u0e27\u0e34\u0e15\u0e17\u0e38\u0e01\u0e40\u0e27\u0e25\u0e32 \u0e1a\u0e48\u0e19\u0e17\u0e38\u0e01\u0e2a\u0e34\u0e48\u0e07\u0e43\u0e19\u0e0a\u0e35\u0e27\u0e34\u0e15 \u0e0a\u0e2d\u0e1a\u0e17\u0e27\u0e34\u0e15\u0e23\u0e39\u0e1b\u0e1c\u0e0a.\u0e15\u0e34\u0e48\u0e07\u0e07\u0e32\u0e19\u0e23\u0e2d\u0e07\u0e1a\u0e48\u0e19\u0e0a\u0e35\u0e27\u0e34\u0e15\u0e07\u0e32\u0e19\u0e2b\u0e25\u0e31\u0e01 \nMy Heart Still Cries\ud83d\udc94","protected":false,"verified":false,"followers_count":40326,"friends_count":347,"listed_count":63,"favourites_count":4720,"statuses_count":130400,"created_at":"Tue Mar 22 14:23:30 +0000 2011","utc_offset":25200,"time_zone":"Jakarta","geo_enabled":false,"lang":"en","contributors_enabled":false,"is_translator":false,"profile_background_color":"FF6699","profile_background_image_url":"http:\/\/pbs.twimg.com\/profile_background_images\/879869496\/38d3c6bc4a40498e5d5d7f4148f8c30b.jpeg","profile_background_image_url_https":"https:\/\/pbs.twimg.com\/profile_background_images\/879869496\/38d3c6bc4a40498e5d5d7f4148f8c30b.jpeg","profile_background_tile":true,"profile_link_color":"B40B43","profile_sidebar_border_color":"FFFFFF","profile_sidebar_fill_color":"E5507E","profile_text_color":"362720","profile_use_background_image":true,"profile_image_url":"http:\/\/pbs.twimg.com\/profile_images\/706828636574232576\/XHNa_j5F_normal.jpg","profile_image_url_https":"https:\/\/pbs.twimg.com\/profile_images\/706828636574232576\/XHNa_j5F_normal.jpg","profile_banner_url":"https:\/\/pbs.twimg.com\/profile_banners\/270380549\/1494161912","default_profile":false,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"retweet_count":186,"favorite_count":11,"entities":{"hashtags":[],"urls":[],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"th"},"is_quote_status":false,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[],"urls":[],"user_mentions":[{"screen_name":"iipum","name":"\u0e0a\u0e2d\u0e1a\u0e1a\u0e48\u0e19 \ud83d\udc51","id":270380549,"id_str":"270380549","indices":[3,9]}],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"th","timestamp_ms":"1496821020657"}

{"created_at":"Wed Jun 07 07:37:01 +0000 2017","id":872356708911382528,"id_str":"872356708911382528","text":"6\u4eba\u307f\u3093\u306a\u9014\u4e2d\u629c\u3051\u3057\u3066\u3044\u3063\u305fw","source":"\u003ca href=\"http:\/\/twitter.com\/download\/iphone\" rel=\"nofollow\"\u003eTwitter for iPhone\u003c\/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":2520343916,"id_str":"2520343916","name":"\u3042\u308a\u3059\ud83c\udf6d","screen_name":"q1ice_","location":"ow:RcDt","url":null,"description":"ow cod R6S bf mhxx Shadowverse etc. Destiny2\u3068\u30a4\u30ab2\u5f85\u3061","protected":false,"verified":false,"followers_count":659,"friends_count":301,"listed_count":14,"favourites_count":3104,"statuses_count":22281,"created_at":"Sat May 24 12:18:01 +0000 2014","utc_offset":32400,"time_zone":"Tokyo","geo_enabled":false,"lang":"ja","contributors_enabled":false,"is_translator":false,"profile_background_color":"C0DEED","profile_background_image_url":"http:\/\/abs.twimg.com\/images\/themes\/theme1\/bg.png","profile_background_image_url_https":"https:\/\/abs.twimg.com\/images\/themes\/theme1\/bg.png","profile_background_tile":false,"profile_link_color":"1DA1F2","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http:\/\/pbs.twimg.com\/profile_images\/868312218214858752\/hTbsa5JJ_normal.jpg","profile_image_url_https":"https:\/\/pbs.twimg.com\/profile_images\/868312218214858752\/hTbsa5JJ_normal.jpg","profile_banner_url":"https:\/\/pbs.twimg.com\/profile_banners\/2520343916\/1490264007","default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[],"urls":[],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"ja","timestamp_ms":"1496821021665"}

{"created_at":"Wed Jun 07 07:37:01 +0000 2017","id":872356708882010112,"id_str":"872356708882010112","text":"@Mankai_Rrai \ub2f9\uadfc\ube73\ub530\uc8e0","display_text_range":[13,18],"source":"\u003ca href=\"http:\/\/twitter.com\/download\/android\" rel=\"nofollow\"\u003eTwitter for Android\u003c\/a\u003e","truncated":false,"in_reply_to_status_id":872356660551061506,"in_reply_to_status_id_str":"872356660551061506","in_reply_to_user_id":831555742243000321,"in_reply_to_user_id_str":"831555742243000321","in_reply_to_screen_name":"Mankai_Rrai","user":{"id":3323785092,"id_str":"3323785092","name":"\ud83c\udf38\uc720\uc740\ud83d\udc30","screen_name":"ensemble_ayuP","location":null,"url":"http:\/\/twpf.jp\/ensemble_ayuP","description":"\u2665\uc544\ucf00\ud638\uc2dc \uc2a4\ubc14\ub8e8+\ud0c0\uce74\ubbf8\ub124 \ubbf8\ub3c4\ub9ac \/ \ud2b8\ub9ad\uc2a4\ud0c0P+\uc720\uc131\ub300\ud558\ucf54\uc624\uc2dc+\ub18d\uad6c\ubd80\ub2e4\uc774\uc2a4\ud0a4\ub9e8 \/ FUB free","protected":false,"verified":false,"followers_count":69,"friends_count":57,"listed_count":2,"favourites_count":666,"statuses_count":58593,"created_at":"Sat Aug 22 09:02:50 +0000 2015","utc_offset":32400,"time_zone":"Seoul","geo_enabled":false,"lang":"ko","contributors_enabled":false,"is_translator":false,"profile_background_color":"FFFFFF","profile_background_image_url":"http:\/\/pbs.twimg.com\/profile_background_images\/660120871772647425\/1d4eQw6n.jpg","profile_background_image_url_https":"https:\/\/pbs.twimg.com\/profile_background_images\/660120871772647425\/1d4eQw6n.jpg","profile_background_tile":false,"profile_link_color":"FA743E","profile_sidebar_border_color":"000000","profile_sidebar_fill_color":"000000","profile_text_color":"000000","profile_use_background_image":true,"profile_image_url":"http:\/\/pbs.twimg.com\/profile_images\/856362256937869313\/XU6I8a9k_normal.jpg","profile_image_url_https":"https:\/\/pbs.twimg.com\/profile_images\/856362256937869313\/XU6I8a9k_normal.jpg","profile_banner_url":"https:\/\/pbs.twimg.com\/profile_banners\/3323785092\/1486033778","default_profile":false,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[],"urls":[],"user_mentions":[{"screen_name":"Mankai_Rrai","name":"\ub77c\ud83d\udc25","id":831555742243000321,"id_str":"831555742243000321","indices":[0,12]}],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"ko","timestamp_ms":"1496821021658"}

Can anyone please suggest me how can i get this done.

Thanks in advance

Mohan V

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

You should first add a custom column with the custom formula

Json.Document([Content])

Then, depend on which Json file you want to query, click the corresponding cell under the new custom column. The subscequent steps are the same as that applied to loading single json file.

Best regards,
Yuliana Gu