none
Split Value column into two columns, numeric and text

    Question

  • I've used unpivot to restructure my table but one of the fields in the group was text.  I now want to split the Value column into two separate columns, one containing the values and one containing the text.  Any help would be appreciated!
    Friday, June 06, 2014 6:59 PM

Answers

  • Are you comfortable using the Advanced Editor? If so, paste this in as a query and you'll be able to see it in action:

    let
        Source = #table({"Column1"},{{1},{2},{"text"},{4},{5}}),
        InsertedCustom = Table.AddColumn(Source, "Custom", each try Number.From([Column1])),
        #"Expand Custom" = Table.ExpandRecordColumn(InsertedCustom, "Custom", {"Value"}, {"Custom.Value"}),
        InsertedCustom1 = Table.AddColumn(#"Expand Custom", "Custom", each if ([Custom.Value] = null) then [Column1] else null),
        RenamedColumns = Table.RenameColumns(InsertedCustom1,{{"Custom.Value", "Value"}, {"Custom", "Text"}}),
        RemovedColumns = Table.RemoveColumns(RenamedColumns,{"Column1"})
    in
        RemovedColumns

    Almost all of this was done through UI clicks. The only trick was adding two custom columns. The first uses the "try" keyword to convert from the original value to a number format. The second looks to see if that was successful and if not, it just takes the original text value.


    Monday, June 09, 2014 10:14 PM

All replies

  • So, you have a single column containing two values that you want to split into two separate columns? Can you give one or more examples showing how the values in the column are currently formatted?
    Sunday, June 08, 2014 8:48 PM
  • Sure, the column contains both numeric values and textual comments - example below:

    4.53

    3.2

    The variance occurred because of extra client activity undertaken in Q3.

    2.3

    2

    and so on

    I want anything numeric in a column titled "Value" - anything that can't be converted into a number belongs in a column "Textual".  The Attribute column would show the name of the metric or the word "Comments".  If the attribute is "Comments", the Value field would be null.  Any other attribute would have a value but would be null in the "Textual" field.

    Hope that makes sense, and thanks for your help!


    • Edited by PowerIsFun Monday, June 09, 2014 1:25 PM
    Monday, June 09, 2014 1:25 PM
  • Are you comfortable using the Advanced Editor? If so, paste this in as a query and you'll be able to see it in action:

    let
        Source = #table({"Column1"},{{1},{2},{"text"},{4},{5}}),
        InsertedCustom = Table.AddColumn(Source, "Custom", each try Number.From([Column1])),
        #"Expand Custom" = Table.ExpandRecordColumn(InsertedCustom, "Custom", {"Value"}, {"Custom.Value"}),
        InsertedCustom1 = Table.AddColumn(#"Expand Custom", "Custom", each if ([Custom.Value] = null) then [Column1] else null),
        RenamedColumns = Table.RenameColumns(InsertedCustom1,{{"Custom.Value", "Value"}, {"Custom", "Text"}}),
        RemovedColumns = Table.RemoveColumns(RenamedColumns,{"Column1"})
    in
        RemovedColumns

    Almost all of this was done through UI clicks. The only trick was adding two custom columns. The first uses the "try" keyword to convert from the original value to a number format. The second looks to see if that was successful and if not, it just takes the original text value.


    Monday, June 09, 2014 10:14 PM
  • Thanks!

    I had managed the numeric column as a custom column already.  I didn't quite follow the #"Expand Custom" part but I used the If statement in the second insert column to check the numeric column as you suggested and it works!  I also decided to use "" instead of null for the string column.

    Appreciate you help!

    Wednesday, June 11, 2014 3:07 PM