Conditionally Split row into multiple Columns

[sree_12]

Hello All,

I need your help in Power Query.

I have a column where values in the column gives description with delimiter.

I want to split the value into four columns and each column should have value as mentioned below.

In the Column1:  check value before delimter and if there is no values then display as No Name.

In the Column2:  check for value after first delimiter and if there is null value's then display value before delimter(Column1) and if there is no values then display as No Name.

In the Column3:  check for value after second delimiter and if after second delimiter has null value then check for value after first delimiter(Column 2) and if there is null value in (Column 2) then display value before delimter (Column 1)and if there is no value in (Column 1) then display as No Name.

In the Column4: We need value after third delimiter and if there is null value after the third delimiter then check for value after second delimiter(Column 3) and if second delimiter(Column3) has null value then check for value after first delimiter (Column 2) and if there is null value's then display value before delimter (Column 1)and if there is no values then display as No Name.

I have a scenario where my table looks like this 

ID	Name
1	A,B,C,D
2	A,B
3	null
4	A,B,C
5	A

And I need result to be like this

ID	Name1	Name2	Name3	Name4
1	A	B	C	D
2	A	B	B	B
3	No Name	No Name	No Name	No Name
4	A	B	C	C
5	A	A	A	A

Any help could be highly appreciated.

[AlB · Accepted solution]

Hi @sree_12 

Place this code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUcdJx1nHRcdVxU4rViVYygoiB2cZANphhAlMI5pmCeEqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"","No Name",Replacer.ReplaceValue,{"Name"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Text.Split([Name],",")),
    maxLen_ = List.Max(List.Transform(#"Added Custom"[Custom], each List.Count(_))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each List.Combine({[Custom],List.Repeat({List.Last([Custom])},maxLen_-List.Count([Custom]))})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Name", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom2", "Name"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4", "Name.5", "Name.6"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"Name.1", type text}, {"Name.2", type text}, {"Name.3", type text}, {"Name.4", type text}, {"Name.5", type text}, {"Name.6", type text}})
in
    #"Changed Type"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[camargos88 · Accepted solution]

Hi @sree_12 ,

Try this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUcdJx1nFRitWJVjKC8MFsYyAbzDCBKQLzTEE8pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Name"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each let _return =
if [Name] = null then List.Repeat({"No Name"}, 4)
else
let _name = Text.Split([Name], ",") in
if List.Count(_name) = 4 then
_name
else
_name & List.Repeat({ List.Last(_name)}, 4 - List.Count(_name)) in

Text.Combine(_return, ";")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"Name", type text}, {"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name"})
in
#"Removed Columns"

[sree_12]

Wow, both of your solutions working as expected 

Thank you very much @AlB @camargos88 

[AlB · Accepted solution]

Hi @sree_12 

Place this code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUcdJx1nHRcdVxU4rViVYygoiB2cZANphhAlMI5pmCeEqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"","No Name",Replacer.ReplaceValue,{"Name"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Text.Split([Name],",")),
    maxLen_ = List.Max(List.Transform(#"Added Custom"[Custom], each List.Count(_))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each List.Combine({[Custom],List.Repeat({List.Last([Custom])},maxLen_-List.Count([Custom]))})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Name", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom2", "Name"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4", "Name.5", "Name.6"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"Name.1", type text}, {"Name.2", type text}, {"Name.3", type text}, {"Name.4", type text}, {"Name.5", type text}, {"Name.6", type text}})
in
    #"Changed Type"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers