split columns with null result

Hello there,

It looks like you are trying to split the column using a line feed string. This refers to a line break, but there is none at your column.

I guess this formula can help you, Otherwise please provide your smple data as table here to provide the solution based on your data

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "transact", Splitter.SplitTextByDelimiter("#(lf)#(lf)", QuoteStyle.Csv), 2)

in
#"Split Column by Delimiter"

Hi yokaso ,
Thanks for Omid_Motamedise reply.
Based on the code you provided, it seems that you want to split the TRANSACT columns to get to the point where all the data has dates and values, if so, you can try the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjAyNjJR2l/LxUIAlkxupEK0H4SUAKzDU01AciqMKS8nyQQhOYQiAfodBIH4hgCjOKUsFmmsKVgkQgimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Transact = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Transact", type text}, {"Value", Int64.Type}}),
    FillDown = Table.FillDown(Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), {"Date"}),
    FilteredRows = Table.SelectRows(FillDown, each ([Date] <> null)),
    GroupedRows = Table.Group(FilteredRows, {"Date"}, {{"AllData", each _, type table [Date=nullable date, Transact=nullable text, Value=nullable number, Index=Int64.Type]}}),
    AddCustom = Table.AddColumn(GroupedRows, "Custom", each let
        TransactList = [AllData][Transact],
        Transact1 = if List.Count(TransactList) > 0 then TransactList{0} else null,
        Transact2 = if List.Count(TransactList) > 1 then TransactList{1} else null
    in
        [Transact1=Transact1, Transact2=Transact2]),
    ExpandCustom = Table.ExpandRecordColumn(Table.ExpandTableColumn(AddCustom, "AllData", {"Value"}, {"Value"}), "Custom", {"Transact1", "Transact2"}),
    RemoveNulls = Table.SelectRows(ExpandCustom, each [Value] <> null),
    #"Renamed Columns" = Table.RenameColumns(RemoveNulls,{{"Transact1", "Transact"}, {"Transact2", "Transactb"}})
in
    #"Renamed Columns"

Final output

Best regards,
Albert He

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