Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pye
Helper I
Helper I

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 get the data to Power Query via "From Text/CSV", Power Query can recognise the it as csv and split the column with column delimeter: [Delimiter=",", Encoding=1252])

idaddressscore
129023new york, USA30

 

However, if I upload the csv to sharepoint and get the data to Power Query via "From Web", Power Query recognise as unstructured text and only load a single column to the editor. I did "Split Column with Delimiter" in the editor, but it is not doing the splitting well via due to occurance of delimiter (comma) in the data.

idaddressscorecolumn1
129023new yorkUSA30

 

Is there anyway to fix it? This is being chosen under the manual column split

pye_0-1666932867449.png

 

Thank you

 

1 ACCEPTED SOLUTION

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"

 

 

View solution in original post

9 REPLIES 9
Luis98
Resolver II
Resolver II

Hi @pye ,

 

I have worked with CSV in Sharepoint many times, i usually do it using the connector from Sharepoint folder. Try with this connector and if you have problems try to upload here a sample and i will try solve it.

Hi @Luis98 

 

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

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

 

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

pye_0-1666953881463.png

 

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:

pye_1-1666954175643.png

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

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

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.

pye_1-1666960146348.png

 

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

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"

 

 

i got it now! thank you so much 🙂

You´re welcome🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors