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

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.

7 Replies

  • KNP's avatar
    KNP
    Super User
    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

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

  • 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