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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Kinga99
Helper II
Helper II

WBS issue in Power Query - cannot change type to text

Hi everyone,

 

I have to do some analysis on the scheduele and I'm having trouble with the WBS column. When I import the csv (or excel), this column has a data type 'Any'. I noticed that majority of the values are on the left, but some are strangely moved to the right of the column. When I try to change type of that column to text (to preserve the structure of the string of numbers), PowerQuery changes some values. Upon loading to model, this causes errors. Not to mention that if I filter the errors out, I loose critical data.

 

Kinga99_0-1633614704349.png

This is after changing the column to text. Same result if I create a custom column and use the function Text.From([WBS]).

 

Kinga99_1-1633614773182.png

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @Kinga99 ,

 

Yes,if you directly change the type to text or use Text.From,you will get a result as below:

vkellymsft_0-1633936884597.png

You could try to take below steps:

Click "Add column">"Column From examples">Using below format:

vkellymsft_1-1633936990349.png

And you will see:

vkellymsft_2-1633937023509.png

For my sample .pbix file,pls see attahched.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

8 REPLIES 8
amirshiloh
Advocate II
Advocate II

amirshiloh_0-1728761394541.png

i found that by adding ' apostrophe sign, at the XLSX file source, before the WBS values that are in potential to have mismatch as a text format, is solve the problem on the power-bi tables.

when i tried to set theses values on the excel as "text format" it doens't has afffect on the power bi tables - whereas adding apostrophe - is the way to solve it (till the power-bi bug will be fixed)

using Excel formula - makes its easy  : (the WBS column is C)

run on the Excel sheet the following formula for the WBS values, apply (drag) that formula all along the WBS values on that column:

=IF(ISNUMBER(C2), IF(MOD(C2, 1)<>0, "'" & C2, C2), C2)

And later, set the column on power bi to text

v-kelly-msft
Community Support
Community Support

Hi  @Kinga99 ,

 

Yes,if you directly change the type to text or use Text.From,you will get a result as below:

vkellymsft_0-1633936884597.png

You could try to take below steps:

Click "Add column">"Column From examples">Using below format:

vkellymsft_1-1633936990349.png

And you will see:

vkellymsft_2-1633937023509.png

For my sample .pbix file,pls see attahched.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

that improves the situation- it reduce the number of lines with that WBS format error, but still havn't solved completely the issue. thanks for that.

ronrsnfld
Super User
Super User

I suspect that, on the Excel worksheet, the contents of row 2 is the result of a formula and not merely entered into the table as 17.1   Because of how Excel computes and stores fractional values, that is likely how the value is stored in Excel.  Hence the conversion you see in PQ.

 

How are those values actually generated in Excel?

edhans
Super User
Super User

Without sample data it is hard to say, but make sure that no previous step automatically added any Change Type steps for that column. For example, by default, Excel will apply a type, even if it is the Any type.

edhans_0-1633621113280.png

Look for any previous Changed Type steps and remove it from that column, or manually change "any" to "text" early on.

Otherwise, post some 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

Hi, thank you for responding,

Per your instructions, I edited the type from any to 'text' at the very beginning, but the same problem occurs. I opened the excel file, and  noticed that all columns are 'General' data type. When I changed the type to text (in Excel) the numbers that appear on the right side in that column, jumped to the left. I think the root of the problem is in Excel and how that date transfers to PBI. I would upload a sample, but I don't see an option for attachments!

Kinga99_0-1633702515196.png

 

See instructions below on sharing 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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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