Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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"
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"