Need help on Pivot/Unpivot/Transpose in Power Query Editor

[suparnababu8]

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

[suparnababu8]

Thanks a lot. It worked for me.

[spinfuzer · Accepted solution]

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