Power Query - Split by Space - Except text
Results 1 to 3 of 3

Thread: Power Query - Split by Space - Except text
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query - Split by Space - Except text

    Is there a way to split by space as a delimiter but only if the space falls between a number?

    So you got some poorly formed excel that has all the data in one column an example I created this. Because if people have different or hyphenated names then that will throw it off so if we can get it to skip the word spaces altogether then it should always hold its columns.

    Code:
    class nuname        score1  score2  score3  score4 
          1 jon smith      43.69   86.20   15.08   41.40
           2 bill van smith   70.56   86.87    6.90   88.77
           3 tanya jones    50.75   42.05   31.16   95.18
           4 ron le bron    36.18   29.95   47.45    8.87
           5 jill jake      93.83   70.50   91.10   52.75
           6 beth mott      60.68   98.70    3.07   28.23
           7 abdul khan     92.57   73.48   84.97   32.88

  2. #2
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - Split by Space - Except text

    Hi there,
    This should work, assuming your data sits in “Table1”
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        RenameTable = Table.RenameColumns(Source,{{"class nuname        score1  score2  score3  score4 ", "Table"}}),
        SeparateText = Table.AddColumn(RenameTable, "Name", each Text.Trim(Text.Remove([Table],{"0".."9","."}))),
        SeparateNumbers = Table.AddColumn(SeparateText, "Numbers", each Text.Remove([Table],{"A".."z"})),
        AddIndex = Table.AddIndexColumn(SeparateNumbers, "Index", 0, 1),
        CreateListFromNumbers = Table.AddColumn(AddIndex, "List", each Text.Split([Numbers], " ")),
        RemoveBlanks = Table.AddColumn(CreateListFromNumbers, "Custom.2", each Table.AddIndexColumn(Table.FromList(List.RemoveMatchingItems([List], {"", null})),"Inde",0,1)),
        Expand = Table.ExpandTableColumn(RemoveBlanks, "Custom.2", {"Column1", "Inde"}, {"Column1", "Inde"}),
        RemCol = Table.RemoveColumns(Expand,{"List"}),
        ChangeType = Table.TransformColumnTypes(RemCol,{{"Column1", type number}}),
        Pivot = Table.Pivot(Table.TransformColumnTypes(ChangeType, {{"Inde", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(ChangeType, {{"Inde", type text}}, "de-DE")[Inde]), "Inde", "Column1", List.Sum),
        Sort = Table.Sort(Pivot,{{"Index", Order.Ascending}}),
        RemCol2 = Table.RemoveColumns(Sort,{"Table", "Numbers", "Index"}),
        Reorder = Table.ReorderColumns(RemCol2,{"0", "Name", "1", "2", "3", "4"})
    in
        Reorder
    Here’s some explanation on the technique behind it:
    https://social.technet.microsoft.com...rum=powerquery

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query - Split by Space - Except text

    That is an interesting solution. Maybe this is something I should do in Python before bringing into excel then it will only be splitting each line into an array type checking concatenate strings and iterating back into a final output.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •