Forum Discussion
Resolver IUNABLE TO WORK WITH JSON FILE IN POWER QUERY EDITOR
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???
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.
1 Reply
- lbendlin
Super User
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.