Forum Discussion
Need help to correct a syntax error in my power query formula
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.
Super UserLet'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.