Forum Discussion
transform clean command
I think I am experiencing problems with the Power BI > Transform > Clean command.
What is the clean command supposed to do please?
I thought it simply removed invisible characters, but I have discovered it is removing rows.
The problem only happens with large amounts of data. See example
There are 16000+ records on the table originally.
When the filter is put before the clean command then the query outputs 9 records.
But when the filter is put after the clean command then the query only outputs 3 records.
I cannot fathom why the 6 records are excluded.
Is there a known problem with the clean command or max number of records ?
Thank you
let
Source = Sql.Databases("Mysql"),
Mydata = Source{[Name="Mydata"]}[Data],
MyTable = Modus{[Schema="dbo",Item="MyTable"]}[Data],
#"Cleaned Text" = Table.TransformColumns(MyTable,{{"Myfield", Text.Clean, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each Text.Contains([Myfield], "Test data"))
in
#"Filtered Rows"
3 Replies
Text.Clean removes unprintable characters from a text string.
Your test is rather indirect: can't you just perform row counts before and after applying Text.Clean?
RowsBefore = Table.RowCount(MyTable)
RowsAfter = Table.RowCount(#"Cleaned Text")
If there is a difference, you can investigate which records are missing by merging the tables.
Otherwise I have no explanation for the missing records.
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
`
