Forum Discussion
Need help to correct a syntax error in my power query formula
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:
- Verified Column Names: I've double-checked the column names to ensure they match the actual names in the data.
- Checked Data Types: I've confirmed that the data types of the columns are compatible for concatenation.
- 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 Triangle4 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 Place9 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
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.
5 Replies
ZhangKunSuper User
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 ReplaceText.Trim is used in the formula to remove the spaces generated by two blank lines.
jgeddesFrom 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"].
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.
- 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.
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}})