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/
Hi Anonymous ,
Does this one already solve your query? 🙂
You can paste the subsequent M code into the advanced editor. Check out the steps on the right to get an idea of what the code is doing:
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" = Table.RemoveColumns(#"Table",{"ID", "SRC TABLE", "TGT TABLE", "KEY"}),
#"Removed Other Columns 1" = Table.SelectColumns(#"Removed Columns",{"column value"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns 1",{{"column value", "column"}}),
#"Removed Other Columns 2" = Table.SelectColumns(#"Removed Columns",{"Status"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns 2",{{"Status", "column"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
#"Transposed Table" = Table.Transpose(#"Appended Query")
in
#"Transposed Table",
ColNames = Table.ColumnNames ( Table.Combine ( #"Grouped Rows"[Grouping] ) ),
Custom1 = Table.ExpandTableColumn ( #"Grouped Rows", "Grouping", ColNames )
in
Custom1
Let me know if this helps you 🙂
/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 CustomLet me know 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/