Forum Discussion
Solved
Filter empty values from multiple columns without impacting the structure of data.
I have a table that has multiple values against an Id. I am looking to filter null values from every Values column without altering teh data in other columns or without altering the structure of the ...
Hi,
Please try this:The M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoNBhJ+pTk5MCpWByIBFXNOzEtMScQhCaV8Uysyk/PBkk4ILqaxThhasEtiGuuM4RIknc74jHXGMBboY5CECz6XuqAoxy6BGkAgSVd8rnTFZaQrVhfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Value1 = _t, Value2 = _t, Value3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Id"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "Null")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
I hope this solve tour problem.
I actually have a larger dataset with about 15 fields to filter null from, if I am doing 'Fill Down' and 'Fill Up' and filtering it's leaving me with inaccurate data. 😕
Hi,
Please try this:
The M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoNBhJ+pTk5MCpWByIBFXNOzEtMScQhCaV8Uysyk/PBkk4ILqaxThhasEtiGuuM4RIknc74jHXGMBboY5CECz6XuqAoxy6BGkAgSVd8rnTFZaQrVhfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Id"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "Null")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
I hope this solve tour problem.