Rearrange data using power query

[Maheshwari]

I have the below sample data in excel.(Either one)

Part	A
1	name
2	namee
3	nameee
Part	B
1	value
2	valuee
3	valueee
4	valueeee
Part	C
1	key
2	keyy

 or 

Part	1	2	3
A	name	namee	nameee
Part	1	2	3	4
B	value	valuee	valueee	valueeee
Part	1	2
C	key	keyy

I wanna convert this into below format(Either one)

Part	A	B	C
1	name	value	key
2	namee	valuee	keyy
3	nameee	valueee
4		valueeee

or

Part	1	2	3	4
A	name	namee	nameee
B	value	valuee	valueee	valueeee
C	key	keyy

[ImkeF · Accepted solution]

Hi @Maheshwari ,

you need to add a column that identifies the group and then pivot on it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKlHSUXJUitWJVjIEsvISc1PBHCMoB8IzhvEgXKg2J7i2ssScUoQ+MA+hEcKF8E0QfBSjnOFGZadWwg0CsoGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1" = _t, #"Column2" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "GroupIdentifier", each if [Column1] = "Part" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"GroupIdentifier"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Part")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[GroupIdentifier]), "GroupIdentifier", "Column2")
in
    #"Pivoted Column"

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKlHSUXJUitWJVjIEsvISc1PBHCMoB8IzhvEgXKg2J7i2ssScUoQ+MA+hEcKF8E0QfBSjnOFGZadWwg0CsoGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]),
    grp = Table.Group(Origine, {"Colonna1"}, {{"part", each _[Colonna2]}, {"row", each _[Colonna1]}}, GroupKind.Local, (x,y)=>Number.From(y=x) ),
    prt=List.Max(grp[row],null, List.Count),
    tfc=Table.FromColumns({prt}&grp[part]),
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(tfc, [PromoteAllScalars=true])
in
    #"Intestazioni alzate di livello"

[Anonymous · Accepted solution]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKlHSUXJUitWJVjIEsvISc1PBHCMoB8IzhvEgXKg2J7i2ssScUoQ+MA+hEcKF8E0QfBSjnOFGZadWwg0CsoGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]),
    posPart=List.PositionOf(Origine[Colonna1],"Part",Occurrence.All)&{List.Count(Origine[Colonna2])},
    splc2=List.Transform({0..List.Count(posPart)-2}, each List.Range(Origine[Colonna2],posPart{_},posPart{_+1}-posPart{_})),
    tfc=Table.FromColumns(splc2),
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(tfc, [PromoteAllScalars=true])
in
    #"Intestazioni alzate di livello"

[ImkeF · Accepted solution]

Hi @Maheshwari ,

you need to add a column that identifies the group and then pivot on it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKlHSUXJUitWJVjIEsvISc1PBHCMoB8IzhvEgXKg2J7i2ssScUoQ+MA+hEcKF8E0QfBSjnOFGZadWwg0CsoGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1" = _t, #"Column2" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "GroupIdentifier", each if [Column1] = "Part" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"GroupIdentifier"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Part")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[GroupIdentifier]), "GroupIdentifier", "Column2")
in
    #"Pivoted Column"