Transpose multiple rows in to multiple columns

[Anonymous]

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 . 

I want to see only own row for a key , columns can be varied.

ID	SRC TABLE	TGT TABLE	KEY	column value	Status
1	SRC	TGT	123	{"src_Typ":"B","tgt_Typ":"B"}	Pass
1	SRC	TGT	123	{"src_colb":"D","tgt_colb":"B"}	Fail
1	SRC	TGT	123	{"src_colc":"B","tgt_colc":"B"}	Pass

Need in this format

ID	SRC TABLE	TGT TABLE	KEY	col1	col2	col3	col4	col5	col6
1	SRC	TGT	123	{"src_Typ":"B","tgt_Typ":"B"}	Pass	{"src_colb":"D","tgt_colb":"B"}	Fail	{"src_colc":"B","tgt_colc":"B"}	Pass

and how we can display the data in power bi automatically when the columns increases/decreases

[Anonymous]

thanks for the quick reply appreciate it. But it how to display all the columns automatically in power bi . When we convert the rows to column , some times it might turn into 5 columns to 100 columns.

How we can automatically diplay the fields instead of manually selecting the fields to the report ?

[tackytechtom]

Hi @Anonymous ,

I do not think this is possible. You always need to provide a visual with certain columns. If the number of columns is changing you need to add or remove those manually from the visual.

I suggest to think about your data design once more 🙂

Do not forget to mark the answer as the solution for the specific query you had here. Thanks!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Appreaicte your insight on this @tackytechtom . Thanks again !

[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/

[Anonymous]

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

[tackytechtom · Accepted solution]

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/