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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors