Split rows mongodb

[Anonymous]

Hello everyone,

i have a collection in mongoDB that is something like this:

 

When i put this in power bi, i couldnt show the parcels name(like parcels 0,parcels 1,parcels 2....) in a column, what im expecting to do is something like this.( i didnt put all the fields that parcel object have in this example, i will put it in power bi)

Is there someway to achieve something like this?

PowerBi File 

thanks

[Anonymous]

i used the parse json in my ODBC connection and get this error message

when i do the same thing in that csv file it works.

anyone knows why this happens?

[KNP]

Are you able to paste the text from the parcels field in the previous step (mask/change any sensitive data)?

The error seems to be giving a fairly big clue where to look but difficult without seeing the data.

[Anonymous]

I will send the file, i exported a CSV from the collection.

Mongo Error 

[KNP]

I think you're running into an encoding issue because of the comma as a decimal separator.

This thread may help...

https://community.powerbi.com/t5/Desktop/Encoding/td-p/1021318 

You need to add column instead of transform and then add the optional encoding...

It still errors for me because of my regional settings but give it a try on your system to see if it works. If it does work I'd suggest testing it works when published to the service also as regional settings can sometimes be an issue there also (usually only tmezones though).

let
  Source = Csv.Document(
    File.Contents("D:\Downloads\MongoError (1)\data34.csv"), 
    [Delimiter = ",", Columns = 2, Encoding = 65001, QuoteStyle = QuoteStyle.None]
  ), 
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {{"_id", type text}, {"parcels", type text}}
  ), 
  #"Inserted Parsed JSON" = Table.AddColumn(
    #"Changed Type", 
    "JSON", 
    each Json.Document([parcels], TextEncoding.Windows)
  )
in
  #"Inserted Parsed JSON"

I hope this works for you. 🤞

[CNENFRNL]

Use Json.Document() to parse each parcel.

[Anonymous]

Thanks for your help, now i have somewhere to start!

[KNP · Accepted solution]

Hi @Anonymous, 

I think I understand. 

Does this achieve what you want?

let
  Source = Csv.Document(
    File.Contents("D:\Downloads\PowerBiFile\billingletters3.csv"), 
    [Delimiter = ",", Columns = 32, Encoding = 1252, QuoteStyle = QuoteStyle.Csv]
  ), 
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {
      {"__v", Int64.Type}, 
      {"_id", type text}, 
      {"active", type logical}, 
      {"billingContacts", type text}, 
      {"changed", type datetime}, 
      {"closed", type logical}, 
      {"company._id", type text}, 
      {"company.cnpj", type text}, 
      {"company.name", type text}, 
      {"companyGroup._id", type text}, 
      {"companyGroup.name", type text}, 
      {"contacts", type text}, 
      {"cost.costCenter", Int64.Type}, 
      {"cost.executionUnity", Int64.Type}, 
      {"cost.natureType", type text}, 
      {"cost.observation", type text}, 
      {"cost.sellingValue", Int64.Type}, 
      {"cost.valueClass", Int64.Type}, 
      {"created", type datetime}, 
      {"info.costCenter", Int64.Type}, 
      {"info.executionUnity", Int64.Type}, 
      {"info.observation", type text}, 
      {"info.valueClass", Int64.Type}, 
      {"name", type text}, 
      {"owner._id", type text}, 
      {"owner.name", type text}, 
      {"parcels", type text}, 
      {"parcelsNumber", Int64.Type}, 
      {"products", type text}, 
      {"project", type text}, 
      {"projects", type text}, 
      {"refunds", type text}
    }
  ), 
  #"Parsed JSON" = Table.TransformColumns(#"Changed Type", {{"parcels", Json.Document}}), 
  #"Expanded parcels" = Table.ExpandListColumn(#"Parsed JSON", "parcels")
in
  #"Expanded parcels"

(you'll need to change the file path back)

Hope this helps.