Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power query issue load without delimiter in one column but Ignore quoted line breaks in CSV

Hi Team,

 

My data looks like this but I want to store it without delimiters

but also I would like to Ignore quoted line breaks.

 

for quoted like breaks I know I can use QuoteStyle.Csv but I

am unable to use this function as I already have 5 arguments.

 

CSV file looks like this

 

ID, Language, Name

083611412,E,Jacob , Newman, Summers
083648173,E,Kylie, Robin, Son " Edwards
08777803,D,Chris, Mason, Tony

 

and I would like to load all in one column

only with line break and ignore quoted line break.

 

Note: the text data contains all text characters and special

characters in the naming convention. Therefore delimiter

can't be used.

 

 

Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    Hello Anonymous ,


    maybe the following code will help you

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcy9DsIgFEDhV7lhvkORRthtB38HdUOGW0sqiXATSNP07cVB5/PlWCv2HcKJ0jTT5BEuFL1waEVj1FbKVm6wxwM9eYAa/RIpIdzmGH0uf9caqVV1x/Ud6uPKQ/gqTvAQ0I8L5fGHtdamUdjh7pVDQThT4WrvnFbh3Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    Split = Table.SplitColumn(Source, "Column", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column.1", "Column.2", "Column.3"}),
    Headers = Table.PromoteHeaders(Split, [PromoteAllScalars=true])
in
    Headers


     Best regards from Germany

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    Hello Anonymous ,


    maybe the following code will help you

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcy9DsIgFEDhV7lhvkORRthtB38HdUOGW0sqiXATSNP07cVB5/PlWCv2HcKJ0jTT5BEuFL1waEVj1FbKVm6wxwM9eYAa/RIpIdzmGH0uf9caqVV1x/Ud6uPKQ/gqTvAQ0I8L5fGHtdamUdjh7pVDQThT4WrvnFbh3Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    Split = Table.SplitColumn(Source, "Column", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column.1", "Column.2", "Column.3"}),
    Headers = Table.PromoteHeaders(Split, [PromoteAllScalars=true])
in
    Headers


     Best regards from Germany

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.