none
Split column into multiple text and number columns RRS feed

  • Question

  • I'm trying to figure out how to split this column into multiple columns with power query. One column for the company name/person name, one for the address, one for the zip.  Some of the addresses have a three or four digit code before the address, which I would like in its own column too.  It's the 170 on the lastname, firstname line.  Does anyone have any pointers on this? I'm familiar with PQ advanced editor, but struggling with this one.  

    COMPANY INC. 195 MAIN ST MYCITY ST 12345

    LASTNAME, FIRSTNAME 170 477 ANY STREET CIRCLE  MYCITY ST 12345

    Thanks for your help!

    Thursday, February 5, 2015 8:41 PM

Answers

  • Here is a better query instead of the second query I wrote above. You still need the source in "Table1" on the worksheet, and to keep the function query "addPipeBeforeNumber" which is described above.

    The result is a single column with pipes before numbers.

    Note that the formula assumes that there are no more than 50 columns after we will split the data by space delimiter. You can change the number inside the formula (In the line that starts with Source1, you will see the number 50 as a parameter in Table.SplitColumn).

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Source1 = Table.TransformColumns(Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter(" "),50),{}, addPipeBeforeNumber),
        #"Combine Columns" = Table.CombineColumns(Source1, Table.ColumnNames(Source1),Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Results")
    in
        #"Combine Columns"


    • Edited by Gil RavivMVP Monday, February 9, 2015 7:13 AM
    • Proposed as answer by Gil RavivMVP Monday, February 9, 2015 6:20 PM
    • Marked as answer by Jiminie Monday, February 9, 2015 6:24 PM
    Monday, February 9, 2015 7:11 AM

All replies

  • Can you provide more lines to give us better context for possible separating rules? For example do we always have a number as the first word in the address? Can we assume that company names don't contain numbers?

    Any chance you have a separate list of the addresses, or names that will help to locate the right section to split?

    Friday, February 6, 2015 9:49 AM
  • No, we don't always have a number as the first word in the address.  I don't have a separate list of addresses either.  Is there was way to split columns whenever there is a number?

    That way it would take this input: 

    LastName, FirstName 170 477 Any Street Circle MyCity ST 12345 

    And split it into columns like this: 

    LastName, FirstName | 170 | 477 | Any Street Circle MyCity ST | 12345

    Friday, February 6, 2015 3:26 PM
  • Hi Jiminie,

    To add pipe character before numeric values, please follow these steps:

    1- Create a table "Table1" from your input.

    2- Click Power Query -> From Other Sources -> Blank Query, and paste the following query formula in the Advanced Editor (under view tab):

    (num) =>
    let
        res =  try Number.From(num)
    in
        if (res[HasError]) then Text.From(num)
        else "|" & Text.From(num)

    3- Rename the function query to "addPipeBeforeNumber".

    2- Click Power Query -> From Other Sources -> Blank Query, and paste the following query formula in the Advanced Editor (under view tab):

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.TransformColumns(Table.FromList(Text.Split([Column1], " ")),{}, addPipeBeforeNumber)), #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Custom.Column1"}), #"Grouped Rows" = Table.Group(#"Expand Custom", {"Column1"}, {{"Combined", each Text.Combine([Custom.Column1]," "), type text}}), #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Column1", "Original data"}, {"Combined", "Results"}}) in #"Renamed Columns"

    Load the query into the worksheet:

    Hope this helps.

    Gil

    • Proposed as answer by Gil RavivMVP Sunday, February 8, 2015 11:33 AM
    Sunday, February 8, 2015 11:30 AM
  • Thanks, this is very helpful!  I'm seeing this error for some entries:

    DataFormat.Error: There were more columns in the result than expected.
    Details:
        Count=1

    Here's the column info that's returning the error: 

    5511 DEL CO JAIL-ALAN WEA 280 PHOEBE LANE, SUI DELHI NY 13753

    Do you have any ideas on that error?

    Monday, February 9, 2015 2:17 AM
  • Here is a better query instead of the second query I wrote above. You still need the source in "Table1" on the worksheet, and to keep the function query "addPipeBeforeNumber" which is described above.

    The result is a single column with pipes before numbers.

    Note that the formula assumes that there are no more than 50 columns after we will split the data by space delimiter. You can change the number inside the formula (In the line that starts with Source1, you will see the number 50 as a parameter in Table.SplitColumn).

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Source1 = Table.TransformColumns(Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter(" "),50),{}, addPipeBeforeNumber),
        #"Combine Columns" = Table.CombineColumns(Source1, Table.ColumnNames(Source1),Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Results")
    in
        #"Combine Columns"


    • Edited by Gil RavivMVP Monday, February 9, 2015 7:13 AM
    • Proposed as answer by Gil RavivMVP Monday, February 9, 2015 6:20 PM
    • Marked as answer by Jiminie Monday, February 9, 2015 6:24 PM
    Monday, February 9, 2015 7:11 AM
  • Thanks, this works great!  What does the {} signify/do in the query?  

    In case anyone else comes across this thread, here's Gil's query split line by line:

    let
        Source = Excel.CurrentWorkbook(){[Name="TestTable"]}[Content],
        #"Split Column by Delimiter" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter(" "), 50),
        Custom1 = Table.TransformColumns(#"Split Column by Delimiter", {}, addPipeBeforeNumber),
        Custom2 = Table.CombineColumns(Custom1, Table.ColumnNames(Custom1), Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Results")
    in
        Custom2

    Monday, February 9, 2015 6:07 PM
  • I am glad it helped.

    Used the {} to send empty list as a second parameter for Table.TransformColumns.

    To learn more, open Query Editor and type =Table.TransformColumns in the formula bar. On Enter you will get the description and examples of this function.

    Monday, February 9, 2015 8:30 PM
  • HI Gil,

    We have column with more than one numbers separated by space or comma or semicolon.

    We need to add the row for each number by keeping all other column value same.

    Here is a original table

    Col1 Col2 Col3
    A B 11 22,33 44; 55
    C D 10    20

    and expected output should be

    Col1 Col2 Col3
    A B 11
    A B 22
    A B 33
    A B 44
    A B 55
    C D 10
    C D 20

    Please let us know the best way to solve this...

    Friday, March 6, 2015 9:23 AM
  • BhaveshG,

    Can you please post a new question as it's not 100% related to this topic. It will help more viewers to find the answer? If your input table is "Table1" (using Ctrl+T, with the headers as above), here is the M expression you need.

    let
         Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
         ToList = Table.TransformColumns(Source, {"Col3", Splitter.SplitTextByAnyDelimiter({" ",";",","})}),
         #"Expand Col3" = Table.ExpandListColumn(ToList, "Col3")
    in
         #"Expand Col3"





    • Proposed as answer by Gil RavivMVP Friday, March 6, 2015 10:50 AM
    • Edited by Gil RavivMVP Friday, March 6, 2015 10:57 AM
    Friday, March 6, 2015 9:54 AM