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 have an Excel Data set with a column like below
Rent
4,525
3,750
ABC
4,100
2,500
When i load this data into Powe BI it is displayed without comma like below
Rent
4525
3750
ABC
4100
2500
I tried different formating but could not have data with numeric(with Comma) and text. Please let me know any quick solution.
Solved! Go to Solution.
Hello @Balkrishnan
The problem you are running into is excel does not actually store the comma. 4,525 is stored as 4525 and formatted with the comma. We can add a column to your table to format the numbers correctly though.
Rent Formatted = IF ( ISERROR ( VALUE ( Sheet1[Rent] ) ), Sheet1[Rent], FORMAT ( VALUE ( Sheet1[Rent] ), "#,#" ) )
Hello @Balkrishnan
The problem you are running into is excel does not actually store the comma. 4,525 is stored as 4525 and formatted with the comma. We can add a column to your table to format the numbers correctly though.
Rent Formatted = IF ( ISERROR ( VALUE ( Sheet1[Rent] ) ), Sheet1[Rent], FORMAT ( VALUE ( Sheet1[Rent] ), "#,#" ) )
You are going to have to split it into different columns. Power BI cannot have multiple data types in a column. It put that entire column as text. You can use the FORMAT() function to change how the numbers look, but they will still be text.
Without knowing the context of that data, I cannot provide a sample. I don't know what the ABC represents. It is a column heading, is every 3rd piece of data alpha numeric, etc.?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingABC is some dummy text i entered. My goal is to have comma in Numeric cell(4,350) and not to touch ABC(Some text Value)
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 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |