excelpowerqueryoledbase

Trying to add a blank space after a column in PowerQuery with OLE

I am trying to do what seems like the most simple thing. I want to take a column in PowerQuery and add a space after. I then am going to CombineColumns (I need the space for formatting). I have tried 3 or 4 methods and numerous syntax (about 3 days trying to track this down). I'm doing this out of dBase using OLE. No errors but it is blanking out the entire column.

qString1 = 'let Source = Pdf.Tables(File.Contents('+'"'+_app.session.oRep.outputfilename+'"'+'), [Implementation='+'"'+"1.2"+'"'+']), ;
step11 = Source{[Id='+'"'+"Page001"+'"'+']}[Data], ;
step21= Table.SplitAt(step11,2), ;
step31=List.Last(step21), ;
step41 = Table.PromoteHeaders(step31, [PromoteAllScalars=true]), ;
step51 = Table.TransformColumns(step41,{"'+Column1Name+'", each ['+Column1Name+']+" "}) in step51'

Column1Name is a variable name for the column header. I have tried to hard code the column name as well as +"ABC". It all blanks out the column. It works perfectly through step41. Thanks in advance.

Solution

  • Generically the method would be

    = Table.TransformColumns(PriorStep,{{"ColumnName", each _ & " ", type text}})

    so for yours, where Column1Name is a variable name for the column header, try

    step51 = Table.TransformColumns(step41,{{Column1Name, each _ & " ", type text}})
in step51