Forum Discussion
Solved
Need help on Pivot/Unpivot/Transpose in Power Query Editor
Hi @fabric_Community
I need a help from all of you.
I have data like below mentioned image-1
| Year | Jan | FEB | MAR | ||||||
| 2021 | ABC | DEF | GHI | ABC | DEF | GHI | ABC | DEF | GHI |
| L&T | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| ETA | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
| CAT | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| KBR | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| Caterpillar | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| D&G | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| Year | Jan | FEB | MAR | ||||||
| 2022 | ABC | DEF | GHI | ABC | DEF | GHI | ABC | DEF | GHI |
| L&T | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| ETA | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
| CAT | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| KBR | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| Caterpillar | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| D&G | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| Year | Jan | FEB | MAR | ||||||
| 2023 | ABC | DEF | GHI | ABC | DEF | GHI | ABC | DEF | GHI |
| L&T | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| ETA | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
| CAT | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| KBR | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| Caterpillar | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| D&G | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
Image-1
I need out put like below mentioned image-2
Image-2
How can I achieve this requirement with the help of Power Query Editor.
Here I'm attaching table for your ready reference.
Thanks in advance..!
Fowmy
Greg_Deckler
Import Table into Power Query with no headers (headers should be Column1 to ColumnN).
I did the replace value blanks to nulls because my data was blank instead of null. You may not need to do that.
Copy and paste the entire code into a blank query to see example.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikxNLFLSUfJKzAOSEOTm6gRn+zoGQdmxOtFKRgZGhkC2o5MzkHRxdQOS7h6eRImAtPvElBoYGJmFAIVMCWKQBtcQRyDbhCAGqXV2BJlrTBCD1Ho7gXxlRBCDzU0sSS0qyMzJAYeTIUEM0uMC8ag70R4lNRaMRmNhEMSC8WgsUDsWYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"Column1"}, { {"Rows", each let tranpose_filldown = Table.FillDown(Table.Transpose(_),{"Column1"}), merge_cols = Table.PromoteHeaders(Table.Transpose(Table.CombineColumns(tranpose_filldown,{"Column1","Column2"},Combiner.CombineTextByDelimiter(";"),"Merged"))), unpivot = Table.UnpivotOtherColumns(merge_cols, {Table.ColumnNames(merge_cols){0}}, "Attribute", "Value"), year = Text.AfterDelimiter(Table.ColumnNames(unpivot){0},";"), add_year = Table.AddColumn(unpivot, "Year", each year), split = Table.SplitColumn(add_year,"Attribute",Splitter.SplitTextByDelimiter(";")), rename = Table.RenameColumns( split, { {Table.ColumnNames(split){0}, "Column1"} } ) in rename } }, GroupKind.Local, (s,c) => Byte.From(c[Column1] = "Year") ), Combine = Table.Combine(#"Grouped Rows"[Rows]) in Combine
2 Replies
Import Table into Power Query with no headers (headers should be Column1 to ColumnN).
I did the replace value blanks to nulls because my data was blank instead of null. You may not need to do that.
Copy and paste the entire code into a blank query to see example.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikxNLFLSUfJKzAOSEOTm6gRn+zoGQdmxOtFKRgZGhkC2o5MzkHRxdQOS7h6eRImAtPvElBoYGJmFAIVMCWKQBtcQRyDbhCAGqXV2BJlrTBCD1Ho7gXxlRBCDzU0sSS0qyMzJAYeTIUEM0uMC8ag70R4lNRaMRmNhEMSC8WgsUDsWYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"Column1"}, { {"Rows", each let tranpose_filldown = Table.FillDown(Table.Transpose(_),{"Column1"}), merge_cols = Table.PromoteHeaders(Table.Transpose(Table.CombineColumns(tranpose_filldown,{"Column1","Column2"},Combiner.CombineTextByDelimiter(";"),"Merged"))), unpivot = Table.UnpivotOtherColumns(merge_cols, {Table.ColumnNames(merge_cols){0}}, "Attribute", "Value"), year = Text.AfterDelimiter(Table.ColumnNames(unpivot){0},";"), add_year = Table.AddColumn(unpivot, "Year", each year), split = Table.SplitColumn(add_year,"Attribute",Splitter.SplitTextByDelimiter(";")), rename = Table.RenameColumns( split, { {Table.ColumnNames(split){0}, "Column1"} } ) in rename } }, GroupKind.Local, (s,c) => Byte.From(c[Column1] = "Year") ), Combine = Table.Combine(#"Grouped Rows"[Rows]) in CombineThanks a lot. It worked for me.
