How to creat a new column which reverses the order of every second row

[Cambo]

Hi All

I am doing some analysis on attendance at AFL matches. The data is pulled from www.afltables.com and we transform this into the following table

I want to be able to include a new column at the end called Opposition. This should replicate the Teams column but reverse the home and away teams.

So, against the row where Sydney is the home team I would have Melbourne in the opposition column and in the Melbourne row it would be Sydney in the Opposition column.

I want this column so that I can set up a page in PowerBI to do my basic weekly analysis. For example, if I filter on Opposition = Melbourne and Team = Sydney I can have a card which shows the average crowd against this opposition. My data goes back to 1897 so I have a long history of attendance.

Thanks
Campbell

[EnterpriseDNA]

Hi @Cambo ,

While waiting for the community to review and respond to your issue, take advantage of Data Mentor. It offers a wealth of tools and resources that could provide immediate solutions and enhance your report-building efficiency.

Cheers,

Enterprise DNA Support Team

[HufferD]

@Cambo,

Do you really want to create a column that reverses the order of every second row? Or, alternatively, do you want to respect the season, venue, date, time and round of the game and identify the home and away teams (and quarter and final scores and win/loss) of each?

You can do this a few different ways.

One way would break the steps into separate queries (but reading the file contents only once) like:

Base

let
    Source = Excel.Workbook(File.Contents("path\to\file\file.xlsx"), true, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Teams", type text}, {"Round", Int64.Type}, {"Home/Away", type text}, {"Quarter Scores", type text}, {"Final Score", Int64.Type}, {"Date", type date}, {"Time", type time}, {"Venue", type text}, {"Crowd", Int64.Type}, {"Season", Int64.Type}, {"Win/Loss", type text}})
in
    #"Changed Type"

Main

let
    Source = Base,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Round", "Date", "Time", "Venue", "Crowd", "Season"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Home

let
    #"Merged Queries" = Table.NestedJoin(Base, {"Season", "Venue", "Date", "Time", "Round"}, Main, {"Season", "Venue", "Date", "Time", "Round"}, "Main", JoinKind.LeftOuter),
    #"Expanded Main" = Table.ExpandTableColumn(#"Merged Queries", "Main", {"Index"}, {"Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Main",{"Round", "Date", "Time", "Venue", "Crowd", "Season"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"Home/Away"] = "Home"))
in
    #"Filtered Rows"

Away

let
    #"Merged Queries" = Table.NestedJoin(Base, {"Season", "Venue", "Date", "Time", "Round"}, Main, {"Season", "Venue", "Date", "Time", "Round"}, "Main", JoinKind.LeftOuter),
    #"Expanded Main" = Table.ExpandTableColumn(#"Merged Queries", "Main", {"Index"}, {"Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Main",{"Round", "Date", "Time", "Venue", "Crowd", "Season"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"Home/Away"] = "Away"))
in
    #"Filtered Rows"

Merged

let
    Source = Table.NestedJoin(Main, {"Index"}, Home, {"Index"}, "Home", JoinKind.LeftOuter),
    #"Merged Queries" = Table.NestedJoin(Source, {"Index"}, Away, {"Index"}, "Away", JoinKind.LeftOuter),
    #"Expanded Home" = Table.ExpandTableColumn(#"Merged Queries", "Home", {"Teams", "Quarter Scores", "Final Score", "Win/Loss"}, {"Home.Teams", "Home.Quarter Scores", "Home.Final Score", "Home.Win/Loss"}),
    #"Expanded Away" = Table.ExpandTableColumn(#"Expanded Home", "Away", {"Teams", "Quarter Scores", "Final Score", "Win/Loss"}, {"Away.Teams", "Away.Quarter Scores", "Away.Final Score", "Away.Win/Loss"})
in
    #"Expanded Away"

Alternatively, you could do this in one query:

OneQuery

let
    Source = Excel.Workbook(File.Contents("path\to\file\path.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers", {{"Home/Away", "HomeAway"}, {"Teams", "Team_Home"}, {"Win/Loss", "WinLoss"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {
        {"Team_Home", type text}, 
        {"Round", Int64.Type}, 
        {"HomeAway", type text}, 
        {"Quarter Scores", type text}, 
        {"Final Score", Int64.Type}, 
        {"Date", type date}, 
        {"Time", type time}, 
        {"Venue", type text}, 
        {"Crowd", Int64.Type}, 
        {"Season", Int64.Type}, 
        {"WinLoss", type text}
    }),

    HomeTeams = Table.SelectRows(#"Changed Type", each [HomeAway] = "Home"),
    AwayTeams = Table.SelectRows(#"Changed Type", each [HomeAway] = "Away"),

    HomeTeamsWithIndex = Table.AddIndexColumn(HomeTeams, "Index", 1, 1, Int64.Type),
    AwayTeamsWithIndex = Table.AddIndexColumn(AwayTeams, "Index", 1, 1, Int64.Type),

    MergedTable = Table.NestedJoin(HomeTeamsWithIndex, {"Round", "Index"}, AwayTeamsWithIndex, {"Round", "Index"}, "AwayTeam", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "AwayTeam", {"Team_Home", "Quarter Scores", "Final Score", "WinLoss"}, {"Team_Opposition_Away", "Quarter_Scores_Away_Merged", "Score_Away", "Win_Loss_Away_Merged"}),

    MergedTableAway = Table.NestedJoin(AwayTeamsWithIndex, {"Round", "Index"}, HomeTeamsWithIndex, {"Round", "Index"}, "HomeTeam", JoinKind.LeftOuter),
    ExpandedTableAway = Table.ExpandTableColumn(MergedTableAway, "HomeTeam", {"Team_Home", "Quarter Scores", "Final Score", "WinLoss"}, {"Team_Opposition_Home", "Quarter_Scores_Home_Merged", "Score_Home", "Win_Loss_Home_Merged"}),

    CombinedTable = Table.Combine({ExpandedTable, ExpandedTableAway}),

    CombinedTableWithFinalScores1 = Table.AddColumn(CombinedTable, "Final_Score_Home", each if [HomeAway] = "Home" then [Final Score] else [Score_Home]),
    CombinedTableWithFinalScores2 = Table.AddColumn(CombinedTableWithFinalScores1, "Final_Score_Away", each if [HomeAway] = "Away" then [Final Score] else [Score_Away]),

    CombinedTableWithQuarterScores1 = Table.AddColumn(CombinedTableWithFinalScores2, "Quarter_Scores_Home", each if [HomeAway] = "Home" then [Quarter Scores] else [Quarter_Scores_Home_Merged]),
    CombinedTableWithQuarterScores2 = Table.AddColumn(CombinedTableWithQuarterScores1, "Quarter_Scores_Away", each if [HomeAway] = "Away" then [Quarter Scores] else [Quarter_Scores_Away_Merged]),

    CombinedTableWithWinLoss1 = Table.AddColumn(CombinedTableWithQuarterScores2, "Win_Loss_Home", each if [HomeAway] = "Home" then [WinLoss] else [Win_Loss_Home_Merged]),
    CombinedTableWithWinLoss2 = Table.AddColumn(CombinedTableWithWinLoss1, "Win_Loss_Away", each if [HomeAway] = "Away" then [WinLoss] else [Win_Loss_Away_Merged]),

    SelectedColumns = Table.SelectColumns(CombinedTableWithWinLoss2, {"Team_Home", "Round", "HomeAway", "Date", "Time", "Venue", "Crowd", "Season", "Team_Opposition_Away", "Final_Score_Home", "Final_Score_Away", "Quarter_Scores_Home", "Quarter_Scores_Away", "Win_Loss_Home", "Win_Loss_Away"}),

    FinalResult = Table.SelectRows(SelectedColumns, each [HomeAway] = "Home")
in
    FinalResult

The approach is basically the same for each. Split the file into home and away then merge it back with itself.

Here’s a look at the data:

I’m sure others can provide better solutions.

[Cambo]

Dear @HufferD

Thanks for the response including the method and code to achieve what I want.

Yes I want to keep these columns of time and venue etc. We are analysing not just the attendance about how this is impacted by who we play where and when.

I assume that if I write this code to split and rejoin the table that it will continue to do this with new data that comes in with a refresh every week? We pull the data every Monday which will come down in the original format.

If this is the case then I can work on implementing your solution.

Thanks
Cambo

[VilmarSchi]

Another option

Test.pbix (17.9 KB)

[Cambo]

@VilmarSchi

Thanks for this solution. I think this might work and is a bit simpler than the other solution. Being relatively new to PowerBI simpler is better at the moment.

Thanks for your help. I will try and give this a go tomorrow when WFH.

Cheers
Cambo

[HufferD]

@Cambo,

If you run into a situation where you are missing a home or away team or their order is reversed you risk letting your data get hopelessly distorted. It’s safer to rely on your grouping columns Season, Date, Time, Venue, and Round to match the home and away teams. But if you’re sure the pairs of rows are always in the same order and you’re never going to miss a home or away row then the simpler option @VilmarSchi proposed is definitely a far better solution.

I attached a test pbix and sample data based on your image. In the pbix, you only need to change the “source” line in the “Base” query to test it out with your data. If your source is an excel sheet, just change the path and filename in the “source” line in the “Base” query. If it’s another format, like csv, you’ll have to change that “source” line to read whatever file you have. You don’t have to change anything else.

To answer your question:

@Cambo

I assume that if I write this code to split and rejoin the table that it will continue to do this with new data that comes in with a refresh every week? We pull the data every Monday which will come down in the original format.

Yes. Whenever you refresh your pbix, that Base query will read the contents of your source file and all the other queries will update.

But, again, much of this is unnecessary iff your source table will always have home and away pairs in the same order and you’ll never have a missing home or away team.

AFL_Matches.xlsx (10.9 KB)
rearrange rows and cols.pbix (110.2 KB)