Forum Discussion
Unpack .blob files in PowerBI
Microsoft EmployeeYou could try connecting to that folder and then adding a custom column that uses Json.Document to parse each line so you can pull out the data.
Regards,
Pat
mahoneypat
Hi - thank you for the suggestion. It returns an error - perhaps because it recognizes the files as .blob and not JSON-format.
This screenshot pretty well shows the general issue with lines that have different attributes and values, when using Comma to separate the columns, where 'DeviceModel' turns up in different columns.If only there was a way to have Power Query go through each line and detect attributes, such as DeviceModel, and put it together in same column. No matter how many attributes were before or after in the line. A solution could be to keep the original column intact and manually create an array of customized columns that extracts values, e.g. with Text.Select to find DeviceModel-attribut and the value. Although not sure how to create the code in this instance.
I am solving it manually right now by having a separate query (linked to data from same Source-query) for the different kind of blob-files, where the attributes and values are in the same order. It's extremely slow though and takes up to 10 hours to load the 1.2 GB of data into Desktop, so I have filtered out and only play with the top 5000 rows.
Jimmy801Community Champion
Hello Anonymous
to me it seems as if some of your data is json some other not. So you could try to integrate in your TransformColumns a try otherwise like TransformColumn(Previousste, {{"Content", each try Json.Document(_) otherwise OtherDataTransformation(_)}})
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Jimmy!
Thank you! I gave that a shot. It worked on the JSON and the only other data transform I knew was the XML-version, which gives a record. I guess I can save this query, disable load and make two tables, one with filtering on Record (unpacking that) and one with keeping the JSONs.Still, this unpacks each file correctly, but it still have the following issues, so would be grateful if you (or anyone else with magic M abilities) could help with amending the solution:
1) It does not appropriately extract and divide the Content attributes and variables into correctly sequenced columns, unless I am missing something? I would still have to use SplitColumns to achieve that (and would need several tables for each structure).
2) It requires several tables and appending them together, as I am doing right now.
3) It's still very slow, takes a few hours to load the total data. I suppose that is just PowerBI query and .blob in general, maybe I need to load it in batches and then set up incremental load. Using BLOB for higher speed was a solution recommended by a consultant, because AppInsights put a maximum of 50,000 rows.
Thank you 🙂