Forum Discussion
Transpose multiple rows in to multiple columns
Source : Sql server. For a Source table name , Target Table name , Key combination , Column value (Comparing the values against the source and target values being stored in this ) , Status . ...
Hi Anonymous ,
How about this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoOcgaSIe4hCkDK0MgYSFbHKBUXJceHVBbEKFnFKDnFKOnEKJWklyCL1ALVBSQWFyvF6hAyJzk/JwmszQVuEFwIapJbYmYOcSYlozkJWQjhplgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"SRC TABLE" = _t, #"TGT TABLE" = _t, KEY = _t, #"column value" = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"SRC TABLE", type text}, {"TGT TABLE", type text}, {"KEY", Int64.Type}, {"column value", type text}, {"Status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "SRC TABLE", "TGT TABLE", "KEY"}, {{"Grouping", each SubGroup(_), type table [column value=nullable text, Status=nullable text]}}), SubGroup = (Table as table) as table => let #"Removed Columns 1" = Table.RemoveColumns(#"Table",{"ID", "SRC TABLE", "TGT TABLE", "KEY"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns 1", {}, "Attribute", "column"), #"Removed Columns 2" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Transposed Table" = Table.Transpose(#"Removed Columns 2") in #"Transposed Table", ColNames = Table.ColumnNames ( Table.Combine ( #"Grouped Rows"[Grouping] ) ), Custom = Table.ExpandTableColumn ( #"Grouped Rows", "Grouping", ColNames ) in CustomLet me know 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
It worked for most of the part , the only part where it missed is value and status column should ne next to each other, but according to your code all the values are displayed and status are next to it.
Because of this I can't know which column value is passed or failed
Hi Anonymous ,
How about this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoOcgaSIe4hCkDK0MgYSFbHKBUXJceHVBbEKFnFKDnFKOnEKJWklyCL1ALVBSQWFyvF6hAyJzk/JwmszQVuEFwIapJbYmYOcSYlozkJWQjhplgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"SRC TABLE" = _t, #"TGT TABLE" = _t, KEY = _t, #"column value" = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"SRC TABLE", type text}, {"TGT TABLE", type text}, {"KEY", Int64.Type}, {"column value", type text}, {"Status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "SRC TABLE", "TGT TABLE", "KEY"}, {{"Grouping", each SubGroup(_), type table [column value=nullable text, Status=nullable text]}}),
SubGroup = (Table as table) as table =>
let
#"Removed Columns 1" = Table.RemoveColumns(#"Table",{"ID", "SRC TABLE", "TGT TABLE", "KEY"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns 1", {}, "Attribute", "column"),
#"Removed Columns 2" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns 2")
in
#"Transposed Table",
ColNames = Table.ColumnNames ( Table.Combine ( #"Grouped Rows"[Grouping] ) ),
Custom = Table.ExpandTableColumn ( #"Grouped Rows", "Grouping", ColNames )
in
Custom
Let me know 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/