Forum Discussion

uayan's avatar
uayan
Frequent Visitor
3 years ago
Solved

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 follo...
Need Help
  • raju_17's avatar
    raju_17
    3 years ago

    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"

raju_17's avatar
raju_17
Regular Visitor
3 years ago

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"