Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Split rows mongodb

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 co...
Need Help
Show and Tell
  • KNP's avatar
    KNP
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
KNP
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

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

    Mongo Error 

    • KNP's avatar
      KNP
      Super User
      5 years ago

      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. 🤞