Forum Discussion
Combine rows into 1 row
Hello all,
I have a table like this
| A | B | C | D |
| FAIL | NULL | NULL | NULL |
| NULL | PASS | NULL | NULL |
| NULL | NULL | FAIL | NULL |
| NULL | NULL | NULL | PASS |
I'd like to convert it to the following (1 row)
| A | B | C | D |
| FAIL | PASS | FAIL | PASS |
There are other columns as well but they have already same values.
Thanks a lot.
Edit: Actually I had pivotted the table. My table was like that. I want it to convert to the above.
| Column1 | Column2 |
| A | FAIL |
| B | PASS |
| C | FAIL |
| D | PASS |
After pivot try transpose and make 1st row as header
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnP09FHSUfIL9UGnYnWiYfwAx+Bg3LJQCtkkTFlkk2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "NULL")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}})
in
#"Changed Type1"
1 Reply
After pivot try transpose and make 1st row as header
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnP09FHSUfIL9UGnYnWiYfwAx+Bg3LJQCtkkTFlkk2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "NULL")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}})
in
#"Changed Type1"