Forum Discussion

SM321's avatar
SM321
Frequent Visitor
2 years ago
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 ...
Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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.

_AAndrade's avatar
_AAndrade
Resident Rockstar
2 years ago

Hi,
try this:


SM321's avatar
SM321
Frequent Visitor
2 years ago

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

 

 

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    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.