Delimiter issue

[Antmkjr]

I have a table like this:

Name	Date	Type
A	7-Jan-25 10-Jan-25 25-Jan-25 25-Jan-25	bus
B		car
C		ship
D		car

And expected output is:,how to do this in power query

Name	Date	Type
A	07-Jan-25	bus
B	10-Jan-25	car
C	25-Jan-25	ship
D	25-Jan-25	car

[dufoq3 · Accepted solution]

Hi @Antmkjr, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLX9UrM0zUyjckzNIAzjUwxmUC1SaXFSrE60UpOQDYQJScWgbnOEG5xRmYBmO+CJB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Type = _t]),
    ColumnsToList = Table.TransformColumns(Source, {}, each List.Select(Text.Split(_, "#(lf)"), (x)=> not List.Contains({"null", ""}, x)) ),
    ColNames = Table.ColumnNames(ColumnsToList),
    Records = List.Buffer(Table.ToRecords(ColumnsToList)),
    Lg = List.Generate(
        ()=> [ x = 0, rec = List.Transform(ColNames, (w)=> Record.FieldOrDefault(Records{x}, w){0}?) ],
        each [x] < List.Count(Records),
        each [ x = [x]+1, rec = List.Transform(ColNames, (w)=> Record.FieldOrDefault(Records{x}, w){0}? ?? Record.FieldOrDefault(Records{0}, w){x}? ) ],
        each [rec]
),
    ToTable = Table.FromRows(Lg, ColNames)
in
    ToTable

[Omid_Motamedise]

Please check this one

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Custom1 = Table.FromColumns({ Source[Name ],Text.Split(Source[Date]{0},"#(lf)"),Source[Type]})
in
    Custom1

[Omid_Motamedise]

To solve this problem, select column Date and go to home tab and then pick split column command (by delimiter) with the below setting

to reach your solution

[Ahmedx · Accepted solution]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLX9UrM0zUyjckzNIAzjUwxmUC1SaXFSrE60UpOQDYQJScWgbnOEG5xRmYBmO+CJB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Type = _t]),
    Replace = Table.ReplaceValue(Source, (x)=>x,(x)=>x, (x,y,z)=>Text.Split(Source[Date]{0}, "#(lf)")  ,{"Date"}),
    Index = Table.AddIndexColumn(Replace, "Index", 0, 1, Int64.Type),
    AddColumn = Table.AddColumn(Index, "tmp", each [Date]{[Index]}),
   RemoveCol= Table.RemoveColumns(AddColumn,{"Date", "Index"})
in
    RemoveCol

[dufoq3 · Accepted solution]

Hi @Antmkjr, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLX9UrM0zUyjckzNIAzjUwxmUC1SaXFSrE60UpOQDYQJScWgbnOEG5xRmYBmO+CJB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Type = _t]),
    ColumnsToList = Table.TransformColumns(Source, {}, each List.Select(Text.Split(_, "#(lf)"), (x)=> not List.Contains({"null", ""}, x)) ),
    ColNames = Table.ColumnNames(ColumnsToList),
    Records = List.Buffer(Table.ToRecords(ColumnsToList)),
    Lg = List.Generate(
        ()=> [ x = 0, rec = List.Transform(ColNames, (w)=> Record.FieldOrDefault(Records{x}, w){0}?) ],
        each [x] < List.Count(Records),
        each [ x = [x]+1, rec = List.Transform(ColNames, (w)=> Record.FieldOrDefault(Records{x}, w){0}? ?? Record.FieldOrDefault(Records{0}, w){x}? ) ],
        each [rec]
),
    ToTable = Table.FromRows(Lg, ColNames)
in
    ToTable