Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sree_12
Frequent Visitor

Conditionally Split row into multiple Columns

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 

IDName
1A,B,C,D
2A,B
3null
4A,B,C
5A

And I need result to be like this

IDName1Name2Name3Name4
1ABCD
2ABBB
3No NameNo NameNo NameNo Name
4ABCC
5AAAA

Any help could be highly appreciated.

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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 

SU18_powerbi_badge

View solution in original post

camargos88
Community Champion
Community Champion

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"



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
camargos88
Community Champion
Community Champion

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"



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Wow, both of your solutions working as expected 

Thank you very much @AlB @camargos88 

AlB
Super User
Super User

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 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors