Results 1 to 3 of 3

Thread: Text.Combine - If Value is Null no Delimiter

  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    39
    Articles
    0
    Excel Version
    2016

    Text.Combine - If Value is Null no Delimiter



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    PHP 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.

    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??

    PHP 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") 

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    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(_)), each if _ = null then null else Text.From(_)) ,", "))

  3. #3
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    39
    Articles
    0
    Excel Version
    2016
    Because you know what your doing, im getting there though. Many thanks you are a Super Star!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •