Need help to correct a syntax error in my power query formula

[Anonymous]

Admitedly I am new to Power Query, I am trying to cleanse an internal database full of duplicate entries. I'm currently stuck at this step to transform the data. 

Problem:

I'm encountering an "Expression.SyntaxError: Token expected ','" error while attempting to create a new column in Power Query Editor. The formula I'm using is:

= Table.TransformColumns(#"Previous Step Name", {{"Trader Uid", each {Account Name} & " " & Text.Clean([Postcode]), type text}})

Context:

I'm working on a data cleaning and transformation task in Power BI. The goal is to create a new column "Trader Uid" by combining the values from the "Account Name" and "Postcode" columns. Seems simple enough but repeatedly getting this error upon various changes to formula. 

Troubleshooting Steps Taken:

1. Verified Column Names: I've double-checked the column names to ensure they match the actual names in the data.
2. Checked Data Types: I've confirmed that the data types of the columns are compatible for concatenation.
3. Reviewed Previous Step: I've verified that the name of the previous step is correct.

Possible Causes and Solutions: The error could be down to my limited Power Query syntax knowledge or data quality or something else. 

Request for Assistance:

I would appreciate any guidance or suggestions from the Power BI community to resolve this issue. Please provide any specific tips or troubleshooting techniques that might help identify and correct the syntax error.

Additional Information:

• Data Source: [My data source is multiple Excel file data exported out of our Salesforce]
• Relevant Data Sample: Here's a small sample of data where my expected outcome is Sl#4, 7 - is a duplicate. 

  Sl Nr.	Account Name	Postcode	Trader Uid	Billing Address Line 1
  1	Argos	KA12 8BH	ArgosKA12 8BH	Ayr Road
  2	Argos	G15 6RX	ArgosG15 6RX	10 Alerdyce Road
  3	Arnold Clark	PH1 3GA	Arnold ClarkPH1 3GA	Clark Superstore The Triangle
  4	Arnold Clark	G32 9QQ	Arnold ClarkG32 9QQ	5 Hamilton Rd
  5	Arnold Clark	G20 7LD	Arnold ClarkG20 7LD	Arnold Clark 53
  6	Arnold Clark	EH11 4DJ	Arnold ClarkEH11 4DJ	1 Bankhead Drive
  7	Arnold Clark	G32 9QQ	Arnold ClarkG32 9QQ	5 Hamilton Rd
  8	Arnold Clark	AB16 6HT	Arnold ClarkAB16 6HT	Clark Autos Ltd Whitemyres Place
  9	Tesco	PA15 1AA	TescoPA15 1AA	Tesco Extra Ayr
  10	Morrisons	G42 9AE	MorrisonsG42 9AE	Morrisons Polloks
  11	Aldi	G41 2AA	AldiG41 2AA	Aldi Shawlands
  12	Lidl	G41 2AA	LidlG41 2AA	Lidl Shawlands

[Anonymous · Accepted solution]

Hi,

Thanks for the solution ZhangKun  and jgeddes  offered, and i want to offer some mroe information for user to refer to.

hello @Anonymous , you can click the 'Accout Name' and 'Postcode' columns,  then click merged columns in 'Add column' tab

Then select split them by using the space.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ZhangKun]

Let's talk about your problem first:

1. The Table.TransformColumns function can only access the current column. For {x, each _}, only the value of column x can be accessed, and other columns cannot be accessed.

2. Text.Clean removes characters with ASCII codes 0 to 30 and 127. If you need to remove blanks, please use Text.Trim (it can only remove blanks at both ends). If you need to achieve your requirements, you can use the following formula:

let
    #"Previous Step Name" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZJRb4IwFIX/yg3PPlAQ1Mc6CGRziSLJlhAfGttIY6VLi9v89yt1jgo+LoHQ8917Tml6q8pD3sTD6iC1+b5gFMB8md+Qqy8KCkmot5tUXuB4MhRBXLzfSC+RD1gwRS971jtD29dIQeFJEHU0cp0jCDM8KPTU+iyD7fmDKd1KxaCszas4aQ6CdU1/T9c9He+ShQEsNptBoacR5OTERSsbKK6/Gj0ICXyYrZJhyEMKUWhj4nFMmiME0+R5UHEwgiVpjjUjFBLFP5lNmv3HqebjELxEMcR5Oag42LkCfG6lhlVL4a3mLTtdFNOwFmQ/voWFWZRM72V3ydjMBcL4hoYa0u9WETBTZq3IN4VXqRTXsrFTNjVHwqlLHyBYSyHkUV8z7GQLyq0dQWB368C9gm1NvgRp6K+tG+4Vp+LO1oF75dp2Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sl Nr." = _t, #"Account Name" = _t, Postcode = _t, #"Trader Uid" = _t, #"Billing Address Line 1" = _t]), 
    Replace = Table.ReplaceValue(#"Previous Step Name", each Text.Trim([Account Name] & " " & Text.Clean([Postcode])), null, (val, old, new) => old, {"Trader Uid"})
in
    Replace

Text.Trim is used in the formula to remove the spaces generated by two blank lines.

[jgeddes]

From the screenshots I see a couple of things. 
Table references cannot contain spaces.

In the screenshot, Renamed Columns should be #"Renamed Columns". 

Column references with non-alpha numeric characters need to be entered the same way. So [Billing Zip/Postal Code] should be [#"Billing Zip/Postal Code"].

[jgeddes]

Adding a column should have the code...

Table.AddColumn(previousStepName, "Trader Uid", each [Account Name] & Text.Clean([Postcode]), type text)

In your code you appear to be attempting to transform an existing column. I would guess the error in your code is due to {Account Name}. It should be [Account Name].

Hope this helps.

[Anonymous]

Thanks @jgeddes for providing a responce with a sample code. Strangely I still get error - see screenshot-1 (same syntax error as before) when I try to add new column, or to transform an existing column (screenshot-2): invalid identifier. 

 

Revised code used:
Add: = Table.AddColumn(#"Renamed Columns", "Trader Uid", each [Account Name] & Text.Clean([Postcode]), type text)
Transform: = Table.TransformColumns(#"Renamed Columns", {{"Trader Uid", each [Account Name] & " " & Text.Clean([Postcode]), type text}})