Pivoting a table in Power Query

[Anonymous]

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-

A	B	C
1	2	3
4	5	6

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

[Anonymous]

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]

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)

[danextian]

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]

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]

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.

[Anonymous]

Hi @danextian ,

Your solution has taken me very close to the solution, however not sure why some rows are not populating and throwing error. Screenshot below

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)

[danextian]

The solution assumes that you wil have this pattern in column1: ABCD..ABCD...ABCD..ABCD not ABC..ABCD..ACD...BCA

[Anonymous]

Oh @danextian  then any thoughts on the expected outcome as shared in the image?
Can we achieve this?

[speedramps]

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]

have to do it in Power Query

[bhanu_gautam]

@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