Unexpected/ Strange Column References

[fox252]

Hello,

I'm experiencing a weird occurrence with column references. The source file comes from an internal reporting portal as .xls ( I know, not ideal). As seen in the Excel screen shot, there are no spaces/ char after the column name. In PQ preview pane header, no issue. It's only when I reference the column I get "{Col Name}#(1f)". If I manually add the #(1f) to the end of the {Col Name}, results in a sytax error. I need to copy it from another step and paste, then no issue. Any idea what is happening here? Maybe something is inbedded in the Excel that I cannot see.

 

[Vijay_A_Verma]

Looks like you used 1 in place of small case L in #(lf)...Could you check this out?

[fox252]

It was in fact an "l" as opposed to "1", they look awfully similar in PQ pane.

The bigger question though is why that suffix on col name?

[Vijay_A_Verma · Accepted solution]

In your Excel, put following formula

=CODE(RIGHT(A1,1)) 

where A1 is that cell header.

You should get the answer - 10

Character 10 is line break character in Excel. This is simulated by pressing ALT+Enter at the end of the line to go to next line.

Hence, if yout type Yearly and press ALT+ENTER, you will go to next line and type Revenue.

You will see following in place of Yearly Revenue

Yearly
Revenue

The official name of Character 10 is Line Feed, hence in PQ, you need to write lf.