How can I replace each x in a column with its column header?

[Monsignor]

Starting with a table like this:

NAME	Illinois	Toronto	Moscow	Belize
Sarah	x
Roberto		x	x	x
Karen	x		x
Olivia
Todd			x	x

<tbody>
</tbody>

The desired result is:

NAME	Illinois	Toronto	Moscow	Belize
Sarah	Illinois
Roberto		Toronto	Moscow	Belize
Karen	Illinois		Moscow
Olivia
Todd			Moscow	Belize

<tbody>
</tbody>

I've unpivoted, outer joined, added index columns, replaced values and found several messy ways to get a result. The problems are with:
- Someone who doesn't have an x will disappear. Like Olivia in my example.
- Adding a new City column doesn't show up with "refresh"

Any help is greatly appreciated!

 

[My Aswer Is This]

If you want a Vba solution you can try this:

Sub City_Names()
'Modified 1-27-18 3:00 PM EST
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:E" & Lastrow)
        If c.Value = "x" Then c.Value = Cells(1, c.Column).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[Monsignor]

Thanks. I appreciate the effort. However, I'm looking for a PowerBI/PowerQuery solution.

 

[My Aswer Is This]

Thanks. I appreciate the effort. However, I'm looking for a PowerBI/PowerQuery solution.

OK. I do not know how to do that. I'm sure someone else here at Mr. Excel may be able to help you.