Text.Combine - If Value is Null no Delimiter

jilbobagins

jilbobagins

Member
Joined
Apr 11, 2019
Messages
80
Reaction score
0
Points
6
Excel Version(s)
2016
Hi,

I'm working on a query where I'm combining a variable amount of columns (following a Pivot) and have come across this line of code

Code: 
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine(List.Transform(List.Skip(Record.FieldValues(_)), each Replacer.ReplaceValue(Text.From(_),null,"")),", ")),

However it give me the Delimiter when the columns fields are null. :nono:

On another query I utilize the code below, but add 'each' and (List.RemoveNulls(_)) and it doesn't populate the delimiter when null. I'm still picking this up, but how would I apply the below to the code above??

Code: 
 #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"5", type text}, {"6", type text}}, "en-GB"),{"Part Number - Copy", "1", "2", "3", "4", "5", "6"},each Combiner.CombineTextByDelimiter(",", QuoteStyle.None)(List.RemoveNulls(_)),"Part")
 
Why not just change the Transform parameter to an expression that will return a null when the column is null?

Code: 
= Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine(List.Transform(List.Skip(Record.FieldValues(_)), [B]each if _ = null then null else Text.From(_)) [/B],", "))
 
Because you know what your doing, im getting there though. Many thanks you are a Super Star!
 
You must log in or register to reply here.
Back
Top