Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am using a table from our SQL Server which has a TEXT field called StoreNo.
These store numbers have leading zeros. For example "00489" and "01157."
When I load the data source into Power BI (import, not direct query), it ignores the fact that StoreNo is a text field and makes teh field a number.
I realize that I can create a column to pad the number with zeros, but since the field in the original table is a text field, I'd like to know how to STOP power BI from assuming this field is a number field and import the values of the field as they are, i.e., have them import as "00489" and "01157."
I need this for many applications, so a solution is greatly appreciated.
Solved! Go to Solution.
Thank you @Icey and @parry2k . I spoke with the creator of the SQL table and while it displayed the leading zeroes in the table, the field has been defined as FLOAT. Since it was a new table, the creator was willing to re-import the data into the SQL table with the field defined as varchar.
I will keep the workarounds for the future since redefining a table is not always going to be an option.
Thank you both!
Hi @ninsights ,
I don't know how to stop Power BI from transforming your "StoreNo." column from Text to Number in your scenario.
But there is one workaround, please check:
1. Create a custom column in Power Query Editor.
= Number.ToText([#"StoreNo."],"00000")
2. Remove your original "StoreNo." column.
3. Rename "Custom" column with "StoreNo.".
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This solution worked perfectly, thanks!
Thank you @Icey and @parry2k . I spoke with the creator of the SQL table and while it displayed the leading zeroes in the table, the field has been defined as FLOAT. Since it was a new table, the creator was willing to re-import the data into the SQL table with the field defined as varchar.
I will keep the workarounds for the future since redefining a table is not always going to be an option.
Thank you both!
Hi @ninsights ,
Glad to hear that you have solved the problem. Please accept your reply above as the solution. Your contribution is highly appreciated.
Best Regards,
Icey
@ninsights in the change type step, make sure it it not converting it to number, and change it to text. Make sure not to add another step to convert to text but change existing step where it change column type. Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I don't actually have any type change steps. Power BI is automatically converting this field to a number. I would like to stop the conversion it's doing when loading my table.
The "advanced editor" displays the following as my entire query. You will see that the only action I've taken is to pick a table. I've typed generic table/database names here.
let
Source = Sql.database("name-sql","ABC"),
dbo_852VendorNameStores = Source{[Schema="dbo",Item="852VendorNameStores"]}[Data]
in
dbo_852VendorNameStores
@ninsights this is interesting, I guess data type in your table is varchar or something like that, correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |