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
jonculp85
Frequent Visitor

Format DirectQuery Data as Currency

For DirectQuery mode in PowerBI, you lose the ability to format the data.  My data source is an azure sql database.  Is it possible to somehow tell PowerBI that you want a specific field to be formatted as currency?  I was wodering if maybe it was possible to add some hint in the sql code to that would tell PowerBI it should be currency.  For example, if I explicitly format the field as numeric then PowerBI enherits the data type.  However, I can not think of how to do this for currency.  I tried mondey in T-SQL, and that did not work (but maybe I did something wrong...).  I tried converting it to text on the T-SQL side and added in '$' and commas in the output, but PowerBI interpreted it as text, which I expected.  How do I get PowerBI data to format as currency while in DirectQuery mode?

2 ACCEPTED SOLUTIONS
Mi2n
Employee
Employee

Hi,

 

I am not sure what could be wrong here. But, it is possible to change the format even in DirectQuery mode by selecting the Modelling ribbon menu -> Format -> Currency.

View solution in original post

Anonymous
Not applicable

So, the solution was decribed by @Mi2n.

 

You select the measure you want to change format, in the modelling (or modeler I don't know I'm using the french Power BI desktop version) :

Devise1.png

you select the format change and choose currency with the region you want.

 

Devise 2.png

 

Sébastien

View solution in original post

7 REPLIES 7
Mi2n
Employee
Employee

Hi,

 

I am not sure what could be wrong here. But, it is possible to change the format even in DirectQuery mode by selecting the Modelling ribbon menu -> Format -> Currency.

Mi2n - Can you ellaborate?  While in DirectQuery mode there is no "Data" section which is where you would normally apply formatting changes using the modeling tab in the ribbon.

Anonymous
Not applicable

Hello @jonculp85

 

you're right, since you use Direct Query with Sql you cannot change the datatype on Power Bi desktop. Here is what you should have :

disabled.png

 

But, if you click "Modifiy Query", in Power Query you should be able to change the datatype. I've managed to do it with Direct Query and Sql Server.



Modifying the data type is not supported in DirectQuery mode. Unless I am mistaken, the data type is not the issue in my case; what I need to change is the number "format".  The client wants to show the currency sign ($) in the visualization for sales, and I agree with them that it makes sense to show it in order to differentiate from counts or "number" of sales.  You can change data type at the visualization layer, but you can not format as currency.  It would be really nice if PowerBI would allow you to change the number format at the visualization layer to add a currency sign.  However, it really should be done at the data layer so that the formatting can propagate to all objects that use the field. 

Anonymous
Not applicable

So, the solution was decribed by @Mi2n.

 

You select the measure you want to change format, in the modelling (or modeler I don't know I'm using the french Power BI desktop version) :

Devise1.png

you select the format change and choose currency with the region you want.

 

Devise 2.png

 

Sébastien

Anonymous
Not applicable

Thank you so much! I was struggling to resolve this issue all day and your fix worked instantly!

Now I understand what y'all are saying.  It is kind of a trick it seems.  So on the "Report" page you click on the field in the query that you want to format, then you can use the "modeling" ribbon to change format.  I tested and it works!  Thank you.  Hopefully Microsoft will expand the DirectQuery functionality soon to be more like the Import mode.

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.