Forum Discussion
Power Query Changing Text Characters to Symbols
Hi,
I'm facing a very weird problem. I have a PBI report where my source is from an Excel file. In the file, there is a column (Huawei cell Name) that has some values like "DHK_X0034_1", "DHK_X2267_1" etc. When I load the table in power BI, power query automatically changes the text to something new like "DHK_X0034_1" to "DHK41" and "DHK_X2267_1" to "DHK>=1".
I've tried changing the format, encoding, and even changed the source file text format and font. But unfortunately, nothing worked.
In the older version of PBI, I never had this issue. After updating the PBI with the latest version the problem popped up.
Need your help. Any support would be highly appreciated.
Thanks & Regards
Sajib
Input (Excel) and Output (PBI) File
Expected Output: Same as input data
Excel Source Data Sample:
Power Query Sample:
As advised, the best course of action here was to post this in the Issues forum where it was seen by the MS team on the Powery product, where it was fixed by the May 2021 release. marking this as the solution to this thread can be seen as solved.
15 Replies
just an observation (which does not solve the enigma) to give some element of further reflection.
- the string x0034 intended as a hexadecimal number is 42 which is the charcode of the "symbol" 4;
- the string x02267 intended as a hexadecimal number is 8807 which is the charcode of the symbol ≧;
- etc.
try reading these strings from an excel file
_X0030_ _X0031_ _X3456_ _X4532_ _X2267_ _X4532_ _X2367_ _X1332_ _X2467_ _X4509_ Anonymous while MS is trying to fix that some temp fix for you
temp fix 1- CSV is a temp fix
Anonymous while MS is trying to fix that some temp fix for you
temp fix # 2 - connect the excel on a ODBC connector
let Source = Odbc.Query( "dbq=C:\Users\sp\Documents\Debug\Input_Data.xlsx;defaultdir=C:\Users\sp\Documents\Debug;driverid=1046;maxbuffersize=2048;pagetimeout=5;dsn=Excel Files", "SELECT `Data$`.`Site Code`, `Data$`.`MRBTS ID`, `Data$`.`Site Name`, `Data$`.`BCF Name`, `Data$`.`Segment Name (SegmentName)`, `Data$`.`Huawei cell Name`#(lf)FROM `C:\Users\sp\Documents\Debug\Input_Data.xlsx`.`Data$` `Data$`" ) in SourceUsing the above query and the same datasource as you, the query returns following
you are going to have to share data.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.Well, I am stuck. I cannot figure out why it is doing this. If I bring the data into Excel's Power Query it works fine, but not in PQ in Power BI. I have also copied and pasted the values to a new workbook at tried it that way - it still fails.
I have confirmed there are no unusual ASCII characters causing this too. ImkeF have you ever seen anything like this? The excel and PBIX file are linked to above.
I am going to think about it some more, and maybe someone else will have a solution, but at this point I'd be inclined to post this to the Issues forum as a bug.
Never experienced that before.
Definitely looks like a bug to me.Hi Anonymous ,
The cause is just like what Anonymous mentioned. And based on my test, this occurs only when there are underscores on both sides of the string like x0034.
Therefore, it is suggested to replace "_" with "-" in your excel file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The fix is to report this to Microsoft in the Issues forum Anonymous - this works fine if you are in Excel and import the data into Power Query. There is a bug in the Power BI PQ interpreter, and you should not have to pre-transform your data before transforming it in Power Query.
Yes, to get up and running immediately, you can do the search/replace, but that is not a long term solution. I have taken the liberty to add it here. BUG: Importing data from Excel incorrectly in Powe... - Microsoft Power BI Community - please vote it up and subscribe to the thread so you can answer any questions. Thanks!
Anonymous I wouldn't spend a lot of time working around this issue. 😁
Anonymous - can you see if the May 2021 update fixes this for you? It was released today, May 12, 2021.
As advised, the best course of action here was to post this in the Issues forum where it was seen by the MS team on the Powery product, where it was fixed by the May 2021 release. marking this as the solution to this thread can be seen as solved.
