Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

Excel_Source_Data_Sample.png

 

 

 

 

 

 

 

 

 

Power Query Sample:

Power_Query_Data_Sample.png

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

15 REPLIES 15
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

@Anonymous I wouldn't spend a lot of time working around this issue. 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@Anonymous - can you see if the May 2021 update fixes this for you? It was released today, May 12, 2021.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
smpa01
Super User
Super User

@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
  Source

 

 

cap4.PNG

Using the above query and the same datasource as you, the query returns following

 

capture2.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

@Anonymous  while MS is trying to fix that some temp fix for you

temp fix 1- CSV is a temp fix

 

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Icey
Community Support
Community Support

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.

 

x0034.PNG

 

 

 

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi,

For the last few days, I was too busy and did not get time to check all the replies. I do think that replacing the "_" is not the permanent solution. And I also appreciate that you have reported it in the issue forum.

 

Thanks

Sajib

Anonymous
Not applicable

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_

 

 

ImkeF
Super User
Super User

Never experienced that before.
Definitely looks like a bug to me.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi,

Thanks for the information. I've uploaded the necessary input and output files. Hope it helps now.

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

have also copied and pasted the values to a new workbook at tried it that way - it still fails. - I guess if you paste only as values, excel connector can read it correctly

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@Anonymous Please let me know if you have any trouble creating a post in the Issues forum. The MS Power BI team monitors that forum and should respond relatively quickly.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors