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

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      i got it now! thank you so much 🙂

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        You´re welcome🙂