Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 . 

 

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

 

IDSRC TABLETGT TABLEKEYcolumn valueStatus
1SRCTGT 123{"src_Typ":"B","tgt_Typ":"B"}Pass
1SRCTGT 123{"src_colb":"D","tgt_colb":"B"}Fail
1SRCTGT 123{"src_colc":"B","tgt_colc":"B"}Pass

 

Need in this format

 

IDSRC TABLETGT TABLEKEYcol1col2col3col4col5col6
1SRCTGT 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

Interesting Links
  • tackytechtom's avatar
    tackytechtom
    2 years ago

    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/

6 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        tackytechtom
        Most Valuable Professional
        2 years ago

        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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tackytechtom
      Most Valuable Professional
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        Appreaicte your insight on this tackytechtom . Thanks again !