powerbipowerquery

how to append two columns in the same table in Power BI

i have four columns: team1 and team2, and also goals 1, and goals2.

i want to make top 5 teams with their goals in the report view for both team1 and team2, how do i append the teams columns together and the goals column together so i can do this, or is there a better way to get the top five of the combination of both columns

enter image description here

Solution

  • Duplicate your query twice : enter image description here

    For the 1st duplicated table, rename team1 to Team and team1_goals to Goals and remove the team2 and team2_goals columns.

    enter image description here

    enter image description here

    Make the same steps for the 2nd table.

    Now you need to append the queries between the 2 tables :

    enter image description here

    Then group your data to sum the scores :

    enter image description here

    Sort the rows in ASC order based on the Sum of the scores :

    enter image description here

    Then keep the first 5 rows :

    enter image description here

    Final result :

    enter image description here