UNABLE TO WORK WITH JSON FILE IN POWER QUERY EDITOR

[PremK]

I have connected Power BI with MongoDB Atlas using SQL ODBC Driver. In my collection(Table)  in mongodb  have 3 documents ( 3 Rows ) , and each document with multiple subdocuments within them, and those subdocuments have further subdocuments inside of them. while i bring the collection (table) Data in to PBI, in Power Query Editor the Subdocuments are in the form of BINARY. I  have tried to convert the Binary  into normal column and row data through Transfrom JSON document option, But it produces Lots and Lots of Duplicate Rows and unable to figure it the duplicate because  now it populates in to 900 rows, also produces lot f errors. 

How to work with this JSON file diredtly connected from MongoDB Atlas???

[lbendlin · Accepted solution]

There are a couple of transforms you can try

Binary.ToText - PowerQuery M | Microsoft Learn

Text.FromBinary - PowerQuery M | Microsoft Learn

Json.FromValue - PowerQuery M | Microsoft Learn

Be aware that transitioning from a hierarchical (NoSQL) to relational (table) environment requires you to make compromises in the way you flatten the hierarchy. You cannot expect this to be a 1:1 transition.