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