none
List.RemoveNulls fails to remove null values when I combine columns which may be empty -- how can I make it work -- is it function precedence?? RRS feed

  • Question

  •  In a dB I like to split up parts of a description.  When reporting to excel -- I want to combine these parts with line breaks to go into a single cell, but sometimes one part may be blank and I don't want to insert a line break when there is a blank in one of the columns.

    Here is my custom column from power query from the function bar -- why doesn't List.RemoveNulls do the trick?

    = Table.CombineColumns(#"Reordered Columns",List.RemoveNulls({"Descript1", "Descript2", "Descript3", "Descript4", "Descript5", "Descript6", "Descript7", "Descript8"}),Combiner.CombineTextByDelimiter("#(lf)"),"Description")

    When I pull the query with the columns not combined.  The values shown in the power query window are null.

    any help on this issue would be greatly appreciated!!

    Wednesday, June 15, 2016 4:37 PM

Answers

  • Another strategy would be to replace the combined column step with the following AddedCustom step - then remove the other columns:

    AddedCustom = Table.AddColumn(<PreviousStepName>, "Description", each Text.Combine(List.RemoveNulls({[Descript1],[Descript2],[Descript3],[Descript4],[Descript5],[Descript6],[Descript7],[Descript8]}),"#(lf)"))

    Wednesday, June 15, 2016 7:11 PM

All replies

  • You're passing list of strings to the function, it won't do anything, and you probably don't need that. Can you change

    Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None)
    with this? :
    (columns) => Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None)(List.RemoveNulls(columns))
    Thanks

    (It will look like this, you won't need to call that function on the column names):

    Table.CombineColumns(#"Changed Type",{"Column1", "Column2", "Column3"},(columns) => Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None)(List.RemoveNulls(columns)),"Merged")



    Wednesday, June 15, 2016 6:27 PM
    Moderator
  • Another strategy would be to replace the combined column step with the following AddedCustom step - then remove the other columns:

    AddedCustom = Table.AddColumn(<PreviousStepName>, "Description", each Text.Combine(List.RemoveNulls({[Descript1],[Descript2],[Descript3],[Descript4],[Descript5],[Descript6],[Descript7],[Descript8]}),"#(lf)"))

    Wednesday, June 15, 2016 7:11 PM