Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Pivoting a table in Power Query

Hi Team,

I have a table like below

And I want to Pivot this table with aggregating, so the final table which is required after transformation in Power Query is like below-

ABC
123
456

 

How can we achieve this? I am getting an error because of duplicate values in Column1. 

Need Help

11 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

     

    Please try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgyxjMAsmawGVN4bJmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    IndexGrouping = Table.AddColumn(#"Added Index", "IndexGrouping", each Number.IntegerDivide([Index], 3), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(IndexGrouping,{"Index"}),
    PivotedTable = Table.Pivot( #"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1","Column2" ),
    #"Removed Columns1" = Table.RemoveColumns(PivotedTable,{"IndexGrouping"})
in
    #"Removed Columns1"

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      hello danextian ,

      you have used number 3 while grouping of indexes.

      In the given example, there were 3 columns, however in the actual PROD data it's not fixed, can be 30, 80 or so on.

      Any improvisations please?

      • danextian's avatar
        danextian
        Super User
        1 year ago

        Hi @

         

        Please see the updated code below

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgyxjMAsmawGVN4bJmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    IndexGrouping = Table.AddColumn(#"Added Index", "IndexGrouping", each
let 
DistinctRows = List.Count(List.Distinct(#"Added Index"[Column1]))
in
 Number.IntegerDivide([Index], DistinctRows), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(IndexGrouping,{"Index"}),
    PivotedTable = Table.Pivot( #"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1","Column2" ),
    #"Removed Columns1" = Table.RemoveColumns(PivotedTable,{"IndexGrouping"})
in
    #"Removed Columns1"

        The update is on the IndexGrouping applied step, with an additional variable that counts the distinct values of Column1 from the previous step. If this doesn’t resolve the issue, please provide sample data that accurately represents the actual data, rather than an overly simplified example. Include any other relevant information.

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Anonymous , Try using this query

     

    let
    Source = YourTable, // Replace with your actual table name
    GroupedRows = Table.Group(Source, {"Column1"}, {{"AllData", each _, type table [Column1=nullable text, Column2=nullable number]}}),
    AddedIndex = Table.TransformColumns(GroupedRows, {"AllData", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}),
    ExpandedTable = Table.ExpandTableColumn(AddedIndex, "AllData", {"Column1", "Column2", "Index"}),
    PivotedTable = Table.Pivot(ExpandedTable, List.Distinct(ExpandedTable[Column1]), "Column1", "Column2")
    in
    PivotedTable

  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    Try this

     

    create 2 measures 

     

    min amount = MIN(yourdata[Column2])
    max amount = MAX(yourdata[Column2])

     

     

    create a matrix visual with

    Switch values to rows

     

    Remove totals

    Rename values to a single spaces

    Please click the [accept solution] and thumbs uup buttons please

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      have to do it in Power Query

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for danextian, speedramps and bhanu_gautam's concern about this issue.

      

    Hi, Anonymous 

    Maybe you can try the following M code, hope it helps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgyxjMAsmawGVN4bJmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    ColumnCount = Table.RowCount(Table.Distinct(Table.SelectColumns(#"Changed Type", {"Column1"}))),
    IndexGrouping = Table.AddColumn(#"Added Index", "IndexGrouping", each Number.IntegerDivide([Index], ColumnCount), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(IndexGrouping,{"Index"}),
    PivotedTable = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(PivotedTable,{"IndexGrouping"})
in
    #"Removed Columns1"

     

     

     

    I have attached the corresponding pbix file below.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, 

      Thanks for taking out time and replying me a solution, however this doesn't help.

      I have attached the actual data in the given path here 

      Please note that we need to Pivot Column K (failed_record.1) for which the Values will be Column L (failed_record.2)