Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |