replace a x with headertext

Question

I want to achieve the following in Power Query: in each cell, replace an x ​​with the text of the header:

Column1 Column2 Column3 Column4 Column5

y z
x y x x
x x z
z z y x z
x z y
x z z
x z z x

This VBA code leads to the goal:

Option Explicit

Sub Replace_x()
Dim rng As Range, c As Range
Set rng = UsedRange ' I know ...
For Each c In rng
If c = "x" Then c = Cells(1, c.Column).Value 'Row_1 = headers
Next c
End Sub

Do you have a solution for me?

Thanks and best regards
Guenther

Answer 1

Guenther, this code solves your task:

= Table.TransformColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, (cellValue)=> if cellValue = "x" then _ else cellValue}))

Where Source is Table with your data - you should make it on previous Query step(s)