Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAs 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Anonymous I wouldn't spend a lot of time working around this issue. 😁
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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
Using the above query and the same datasource as you, the query returns following
@Anonymous while MS is trying to fix that some temp fix for you
temp fix 1- CSV is a temp fix
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!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,
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
just an observation (which does not solve the enigma) to give some element of further reflection.
try reading these strings from an excel file
_X0030_ |
_X0031_ |
_X3456_ |
_X4532_ |
_X2267_ |
_X4532_ |
_X2367_ |
_X1332_ |
_X2467_ |
_X4509_ |
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportinghave 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
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting