Forum Discussion

Hamdan1234's avatar
Hamdan1234
Helper III
4 years ago
Solved

Column Combine to one

I had three columns of courses information,two of them merged and now look like this:

A1
A2
A3
A4
B1
B2
B3
B4

Now I want to merge this column with another table column but in that table both online and classroom courses are in different column as below:

UserClassroomOnline
AlphaB1A1
BetaB2A2
GammaB3A3
RayB4A4

I want to get both these column of online and classroom to be in same column. The outcome should look like below table.

OutcomeOutcome
AlphaB1
BetaB2
GammaB3
RayB4
AlphaA1
BetaA2
GammaA3
RayA4

 

 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi,@Hamdan1234

    You can try the following steps:

    1. Create two tables, as shown in the figure below.

    2. Select the classroom and online columns in the table，Transform—Unpivot Only Selected Columns

    3. Merge Table and course, and the results are shown in the figure.

     

    4. Select display only user, and the result is as shown in the figure.

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

    Best Regards,

    Charlotte Zhang

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Hamdan1234 , In the second table, unpivot the second table and remove the additional column

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswpyEhU0lFyMgQSjoZKsTrRSk6pJWAhI5CQEVjIPTE3FyxmDBIzBosFJVaCRExAIiZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Classroom = _t, Online = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Classroom", type text}, {"Online", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"User"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"
  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi,@Hamdan1234

    You can try the following steps:

    1. Create two tables, as shown in the figure below.

    2. Select the classroom and online columns in the table，Transform—Unpivot Only Selected Columns

    3. Merge Table and course, and the results are shown in the figure.

     

    4. Select display only user, and the result is as shown in the figure.

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

    Best Regards,

    Charlotte Zhang

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