Forum Discussion
Replace null values in multipe columns with a different column
Hi, I am trying to replace the null values in only one of several columns with a single value from the row. This is to help produce a heiarchy (and I am not able to do this in DAX due to data integrity issues, so am left with the conundrum in Power Query).
Each person has a name, and a reporting hierachy as defined in L1, L2, L3 and L4.
I need to write the Name into the 1st of the L2, L3 or L4 columns which is not null, for example, the L2 column is null for Ben, and so I'd like write "Ben" into the L2 colum. A similar process would work for each row, where because the L2 column already has a name, the first null column is L3, and therefore L3 would contain "Fred"
The resulting table would look like (and I have highlighted the changes)
I've tried a few options but cannot get it to work. In my actual real-world scenario I have 8 levels 🙂
Hello - this is one way you can do it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrNU9JRci3KTAZSUBSrE63kVpSagpCAqILJeWXm5laiS0I1gBUEJ+eXlGAaGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, L1 = _t, L2 = _t, L3 = _t, L4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"L1", type text}, {"L2", type text}, {"L3", type text}, {"L4", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"L2", "L3", "L4"}), Custom3 = Table.AddColumn ( #"Replaced Value", "FieldValues", each Record.FieldValues ( _ ) ), ReplFirstNull = Table.AddColumn ( Custom3, "New", each let varFirstNonNullPosition = List.NonNullCount ( [FieldValues] ), ReplaceFirstNull = Text.Combine ( List.ReplaceRange ( [FieldValues], varFirstNonNullPosition, 1, { [FieldValues]{0} } ), "|" ) in ReplaceFirstNull ), #"Removed Other Columns" = Table.SelectColumns(ReplFirstNull,{"New"}), #"Split Column by Delimiter" = Table.SplitColumn ( #"Removed Other Columns", "New", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), Table.ColumnNames ( Source ) ) in #"Split Column by Delimiter"
4 Replies
Hi,
Table.FromRows(
List.Transform(
Table.ToRows(Source),
each List.RemoveNulls(_) & {List.First(_)} & List.Repeat({null}, List.Count(_)-List.NonNullCount(_)-1)
),
Table.ColumnNames(Source))Stéphane
Hello - this is one way you can do it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrNU9JRci3KTAZSUBSrE63kVpSagpCAqILJeWXm5laiS0I1gBUEJ+eXlGAaGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, L1 = _t, L2 = _t, L3 = _t, L4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"L1", type text}, {"L2", type text}, {"L3", type text}, {"L4", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"L2", "L3", "L4"}), Custom3 = Table.AddColumn ( #"Replaced Value", "FieldValues", each Record.FieldValues ( _ ) ), ReplFirstNull = Table.AddColumn ( Custom3, "New", each let varFirstNonNullPosition = List.NonNullCount ( [FieldValues] ), ReplaceFirstNull = Text.Combine ( List.ReplaceRange ( [FieldValues], varFirstNonNullPosition, 1, { [FieldValues]{0} } ), "|" ) in ReplaceFirstNull ), #"Removed Other Columns" = Table.SelectColumns(ReplFirstNull,{"New"}), #"Split Column by Delimiter" = Table.SplitColumn ( #"Removed Other Columns", "New", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), Table.ColumnNames ( Source ) ) in #"Split Column by Delimiter"BenHoward Can you please review the proposed solution and let me know if this answers your question? Thanks!
Hi jennratten , thanks for the answer, it helped a lot.
