none
how to handle nulls while splitting a column into 2 columns using delimeter RRS feed

  • Question

  • hi all,

     i have a value with datatype text as below:

    InternalCode:

    84140601#Service Management
    Vacation

    67890172

    now, i need to split this internalcode column into 2 columns Code and project .I splitted by #(delimeter). But, here in second row Vacation does not have any value for code column like 1st row.

    in third row ,i have value for code but not for project column..

    how can i handle null values here without having delimeter # in data

    please help. thanx in advance..


    lucky


    • Edited by LuckyLucky12 Wednesday, June 7, 2017 5:45 AM
    • Moved by Darren GosbellMVP Wednesday, June 7, 2017 7:13 AM This is a power query question
    Wednesday, June 7, 2017 5:23 AM

Answers

  • If you are missing delimiters in both cases it gets pretty hard to fix. We need some sort of business rule to follow. Assuming that codes would be numeric and projects will not be you could use logic like the following:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type any}}),
        #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Data", type text}}, "en-AU"),"Data",Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv),{"Data.1", "Data.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Code", each try Number.FromText([Data.1]) otherwise ""),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Description", each if Value.Is(Value.FromText([Data.1]), type text) then [Data.1] else [Data.2]),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Data.1", "Data.2"})
    in
        #"Removed Columns"


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by LuckyLucky12 Wednesday, June 7, 2017 7:50 AM
    Wednesday, June 7, 2017 7:15 AM

All replies

  • If you are missing delimiters in both cases it gets pretty hard to fix. We need some sort of business rule to follow. Assuming that codes would be numeric and projects will not be you could use logic like the following:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type any}}),
        #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Data", type text}}, "en-AU"),"Data",Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv),{"Data.1", "Data.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Code", each try Number.FromText([Data.1]) otherwise ""),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Description", each if Value.Is(Value.FromText([Data.1]), type text) then [Data.1] else [Data.2]),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Data.1", "Data.2"})
    in
        #"Removed Columns"


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by LuckyLucky12 Wednesday, June 7, 2017 7:50 AM
    Wednesday, June 7, 2017 7:15 AM
  • thanx much Darren Gosbell, i worked for my requirement perfectly..

    lucky


    Wednesday, June 7, 2017 7:50 AM