Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Delimiter issue

I have a table like this:

Name DateType 
A7-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 DateType 
A07-Jan-25bus
B10-Jan-25car
C25-Jan-25ship
D25-Jan-25car
   
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi Anonymous, 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

     

  • Ahmedx's avatar
    Ahmedx
    1 year ago

    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

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    Hi Anonymous, 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

     

  • Ahmedx's avatar
    Ahmedx
    Super User
    1 year ago

    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
  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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