Forum Discussion
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 data (I've Pivoted the rows into columns and would want to maintain the structure as is.)
Here's the sample data
Id | Value1 | Value2 | Value3 |
A | US | Null | Null |
A | Null | Canada | Null |
A | Null | Null | Mexico |
B | Mexico | Null | Null |
B | Null | Mexico | Null |
B | Null | Null | Mexico |
C | Canada | Null | Null |
C | Null | Mexico | Null |
C | Null | Null | US |
D | Mexico | Null | Null |
D | Null | US | Null |
D | Null | Null | Canada |
E | Canada | Null | Null |
E | Null | US | Null |
E | Null | Null | US |
Required output
Id | Value1 | Value2 | Value3 |
A | US | Canada | Mexico |
B | Mexico | Mexico | Mexico |
C | Canada | Mexico | US |
D | Mexico | US | Canada |
E | Canada | US | US |
I've tried replacing the null values with a placeholder values and tried unpivoting but that was altering the structure of data. Tried creating custom columns with if else condition but that didn't work either. Any input is appreciated.
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.
4 Replies
Hi,
try this: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.
Hi SM321 ,
Please try to follow the steps below:
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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Value1", type text}, {"Value2", type text}, {"Value3", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Null",null,Replacer.ReplaceValue,{"Value1", "Value2", "Value3"}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Replaced Value", {"Value1", "Value2", "Value3"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Only Selected Columns", List.Distinct(#"Unpivoted Only Selected Columns"[Attribute]), "Attribute", "Value") in #"Pivoted Column"An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
