Forum Discussion
transform clean command
Hi Ramps,
Have you tried the solution provided by MarcelBeug above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?
If you still have any question on this issue, feel free to post here. :smileyhappy:
Regards
Thank you for your kind replies. It is very much appreciated, thank you.
The cause was not the CLEAN command. Sorry that was a red herring.
Let me explain the problem ….
The first query contained payments (date, payment ref, amount, etc).
second query contained the payment details (payment ref, account details, etc).
When the first query was merged with the second query by payment ref using a Left Outer join (all from first, matching from second) and the table was expanded to get the account details then a lot of the exactly matching records were missing.
The problem was solved by sorting both queries by payment ref before doing the merge, but I still don’t know what caused the fault.
Whilst sorting is an old EXCEL and programmers trick before merging files, it should not be necessary for Power BI MERGE and I think I have stumbled across a bug here!
Usually the payment ref is a name or account number like “123456” or “John Smith”, but sometimes it contains free text like “Refund for Fred Brown’s order #143* & #150:/EOF~B = £50.00?” .
This is a fictitious example but I have seen similar problems with other programming languages that mistakenly process an reserved character like asterisk, colon, quote, hyphen, tilde, question mark, backslash or a reserve word like union or order or EOF within the data as a command.
You may recall EXCEL up to version 2010 could not search for strings containing “?” and had to search for “~?” instead.
I have a hunch …. and it is merely a hunch …. that the Power BI internal sql engine incorrectly processes some of this free text as a reserved word, but I have been unable to identify specifically which set of characters triggers the fault.
Has anyone else experienced similar weird problems with Power BI MERGE?
It is potentially a serious problem if financial or medical records are missing !
I can replicate the fault with the data for a MS engineer if they want to investigate the matter but sorry I can not post the confidential payment details on this community forum.
In the meantime I will always sort datasets before merging them and check the results carefully for missing records.
Sorry the fault was originally raised with a "clean command" tag and not "merge command".
Thanks again, Ramps
`