I would like to create a new table where selected columns combined in one column then append new rows based on the cell value across the selected columns for example,

I want to convert the above table into this table below

I would be thankful if anyone can help me to solve this.

CodePudding user response：

If your table is a list of lists, for each element of a column you want to merge, create a new row and append it.

This function I made takes as arguments the table you want to treat, the new header of this table (must be a list) and the columns you want to merge and in the order you want to merge them.

def mergeCols(table, new_header, *cols):
    new_table = [new_header]
    for row in table[1:]:
        for idx in cols:
            if row[idx] != '-':
                new_row = [None if i in cols else row[i] for i in range(len(row))]
                new_row[cols[0]] = row[idx]
                new_row = [new_row[i] for i in range(len(new_row)) if i not in cols[1:]]
                new_table.append(new_row)
    return new_table

CodePudding user response：

I figured out one solution in Power Query

1st - Merge the selected columns by clicking Merge Columns, choose comma as the separator. Name the column as Status.

2nd - Select the merged column (Status) then click Split column choose by delimiter. Choose comma as the delimiter. Split at choose Each occurence of the delimiter click Advanced options and choose rows