Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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"
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"
Wow, both of your solutions working as expected
Thank you very much @AlB @camargos88
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