Forum Discussion

FelipMark's avatar
FelipMark
Helper II
3 years ago
Solved

Wipe lines in power query

I have a problem, where I have the following table: | ID | A | B | C | D | |----|-----|-----|-----|-----| | 1 | aaa | null| null| null| | 1 | null| bbb | null| null| | 1 | null| null...
Need Help
  • foodd's avatar
    foodd
    3 years ago

    Try this:

     

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyBGInIHYGYhelWJ1oJQVDBQUgRyExMRFM55Xm5GDQyAphEklJSZgasCmE0cnJyQpEKYTRKSkpChCFRsS60YhYNxoR60YcClHdGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID | A   | B   | C   | D   |" = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> " null")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"