Combine text from all available columns into one in Power Query

[danextian]

Hi All,

I've been trying to solve this on my own with no luck.

I have a table with many columns. Some colums have nulll values, some don't. I want to remove all rows with just null values in all columns. Column is nulll, column 2 is null, column 3 is null so on and so forth... so if the concatenation of all these columns result to null, the row will be removed. 

I wan to do the concatenation without having combine the columns one by one using ampersands or Text.Combne and Text.From

Would be great if this is possible.

[MarcelBeug · Accepted solution]

You can just remove blank rows:

Resulting code:

let
    Source = Table.FromColumns({{1,null,3},{null,null,null}},type table[Number1 = number, Number2 = number]),
    #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

This checks both on nulls and "". If you only want to check for nulls, then you can adjust the code accordingly.

[v-huizhn-msft · Accepted solution]

Hi @danextian,

I reproduce using my simple table.



You can create a custom column using the formula.


Then remove the rows based on the custom column. Please see my Query statement below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEysQASQFasDlDAFMgGIkswDyRuZAwkLSCSRhCFOkrmMGkQAjFNwEqBhJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if(([Column1]=null)and([Column2]=null)and([Column3]=null)) then null else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> null and [Custom] <> "")
in
    #"Filtered Rows"

I will get the expected result.



Best Regards,
Angelia

[vanessafvg]

@danextian never tried it myself but @MarcelBeug has given me a formula before with list in power query maybe that can help?

https://msdn.microsoft.com/en-us/library/mt296612.aspx

[MarcelBeug · Accepted solution]

You can just remove blank rows:

Resulting code:

let
    Source = Table.FromColumns({{1,null,3},{null,null,null}},type table[Number1 = number, Number2 = number]),
    #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

This checks both on nulls and "". If you only want to check for nulls, then you can adjust the code accordingly.