Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Issue with Split Column with Delimiter from Sharepoint CSV

Hi   I have a csv file with 14113 columns to load to Power Query. It goes something like this "id","address","score" "129023","new york, USA","30"   If I download the csv file to my local and g...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Yes, to this binary type column you have to apply the formula i told you in the second message.

     

    Create a custom column and write that:

     

    Csv.Document([Content],[Delimiter=",", Encoding=1252])

     

    This would be the code:

     

    let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"Custom column added" = Table.AddColumn(#"Filtered Rows", "Custom Column", each 
Csv.Document([Content],[Delimiter=",", Encoding=1252]))

in
    #"Custom column added"

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 

 

Thanks for the suggestion. I am facing the same issue if connected to Sharepoint folder. It seems the issue starts when "alt-enter" is used in a cell.

 

https://we.tl/t-3JVvWglUzb

 

I have a test csv here, the last row column T (address) causing the issue if I split the columns manually with delimeter after connected to Sharepoint. However, it works well if I connect the file via local csv, when Power Query did the splitting.

 

Thanks

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Ok, i am trying with the CSV you sent, i think the problem you had is with the Encoding. Try to use this formula to extract de Csv file from the Sharepoint folder, adding a custom column:

 

Csv.Document([Content],[Delimiter=",", Encoding=1252])

 

This should be work, i am getting 6 rows with 14114 columns

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I got a little confused, when the "sharepoint folder", do you mean this?

     

    it asked for my site url, and I did the same to extract the file with the code:

    let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"test online csv_https://jardon sharepoint com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/" = #"Filtered Rows"{[Name="test online.csv",#"Folder Path"="https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/"]}[Content],
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"test online csv_https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/",null,null,1252)})
in
    #"Imported Text"

     this is what I received:

     then I added your code, but I get an error: Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type.

    let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"test online csv_https://jardon sharepoint com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/" = #"Filtered Rows"{[Name="test online.csv",#"Folder Path"="https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/"]}[Content],
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"test online csv_https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/",null,null,1252)}),
    #"Column Split" = Csv.Document(#"Imported Text",[Delimiter=",", Encoding=1252])
in
    #"Column Split"

     

    I suspect my code is "column split" is wrong... 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Ok, when you import the folder from Sharepoint you have to use the Csv.Document, not the SharePoint.files.

       

      let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data"))
    #"Other columns removed" = Table.SelectColumns(Origen,{"Content"}),
    #"Custom column added" = Table.AddColumn(#"Other columns removed", "Custom Column", each Csv.Document([Content],[Delimiter=",", Encoding=1252]))

in
    #"Custom column added"

      Try this

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hey, thanks for tips!

         

        I can't get it yet, pretty sure I missed some steps, because when I extract the file, it is binary, and there is an error of converting type binary to text. (Expression.Error: We cannot convert a value of type Binary to type Table.)

         

         

        let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"The Source" = #"Filtered Rows"{[Name="test online.csv",#"Folder Path"="https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/"]}[Content],
    #"Custom column added" = Table.AddColumn(#"The Source", "Custom Column", each Csv.Document([Content],[Delimiter=",", Encoding=1252]))
in
    #"Custom column added"

         

         

        this is shown in #"Filtered Rows" step, I think the source is still binary.

         

        Is there any way to convert it to table and fix the error? Thanks!