cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Append columns from same table

I have few columns of same table 

 

Table A

ColumnX1  ColumnX2  ColumnX3  ColumnY1 ColumnY2 ColumnY3

A               B              C                    D               E                F

 

I wanted them as :

 

ColumnX     A  B  C

ColumnY     D E  F

 

I can not create a sperate table and append it because there are so many columns and i cant create too many tables.

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@Anonymous,

 

To achieve your requirement, you may follow steps below:

 

Firstly, click ColumnX1, ColumnX2, ColumnX3, ColumnY1, ColumnY2, ColumnY3-> click Transform-> Unpivot Columns.

Capture.PNG 

 

Then click Split Column by number of Characters.

2.PNG 

1.PNG  

 

Finally click Attribute.2 then click Pivot Column:

3.PNG 

 

The result is like below:

4.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@Anonymous,

 

To achieve your requirement, you may follow steps below:

 

Firstly, click ColumnX1, ColumnX2, ColumnX3, ColumnY1, ColumnY2, ColumnY3-> click Transform-> Unpivot Columns.

Capture.PNG 

 

Then click Split Column by number of Characters.

2.PNG 

1.PNG  

 

Finally click Attribute.2 then click Pivot Column:

3.PNG 

 

The result is like below:

4.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Please see if this helps

 

You can see the Steps from Query Editor in attached file

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnX1 ", type text}, {"ColumnX2 ", type text}, {"ColumnX3 ", type text}, {"ColumnY1", type text}, {"ColumnY2", type text}, {"ColumnY3", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Extracted First Characters" = Table.TransformColumns(#"Transposed Table", {{"Column1", each Text.Start(_, 7), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted First Characters", {"Column1"}, {{"all", each _[Column2], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"all", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "all", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"all.1", "all.2", "all.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"all.1", type text}, {"all.2", type text}, {"all.3", type text}})
in
    #"Changed Type2"

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors