cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VeeLearnMSFT
Post Partisan
Post Partisan

Format to a table and add dollar values with proper formatting

Hello, all I am having a hard time trying to make this come out pretty out of the excel sharepoint file.  I cannot get it to format to dollars, commas, nor can I figure out how to put it in nicely spacing with headers.  I have been reading about Markdowns but not quite sure how to get it to work.  

 

The flow attempts to search for specialty in the excel table and result the specialty and the perentile ranges for the dollar values.  I have separated each column by "-" so you can see how it sort of should look like but it also needs headers.  The headers would be:

 

Specialty                  25th              50th               65th                75th              90th

Family Medicine    $222,740.48    $261,122.78   $295,744.46    $321,726.8    $382,120.2

 

 

VeeLearnMSFT_0-1652095076863.png

 

PVA Output:

VeeLearnMSFT_1-1652095115516.png

 

Can someone please provide an example so I can try to replicate?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Expiscornovus
Super User
Super User

Hi @VeeLearnMSFT,

 

Try turning it into a float before you format it.

formatnumber(float(outputs('Get_a_row')?['body/25th ']), 'C2', 'en-us')

 

View solution in original post

Expiscornovus
Super User
Super User

Hi @VeeLearnMSFT,

 

In that append to string variable 2 action use expressions for every field to add the formatting for every dollar value. Try this format:

formatnumber(float(item()?['25th ']), 'C2', 'en-us')

 

View solution in original post

10 REPLIES 10
Expiscornovus
Super User
Super User

Hi @VeeLearnMSFT,

 

To format as a table you could use MarkDown. I have written a blog about this in these forums. Have a look at it and let me know when you need additional help configuring this:

https://powerusers.microsoft.com/t5/Power-Virtual-Agents-Community/How-to-use-mark-down-to-format-a-...

 

For the formating to dollars you could use a formatnumber function. In your case the C2 format with en-us would probably be sufficient:

formatnumber(222740.48, 'C2', 'en-us')
VeeLearnMSFT
Post Partisan
Post Partisan

@Expiscornovus I will try this.  I dont know if this is tacky or unfavored in this community but if this is unacceptable please correct me as some forums dont like jumping topics.  

 

Initially when I posted this example, I did it quite differntly.  In the above example I have filtered that table so that i tonly gives the dollar values.  but the orignal table has 3 lines for the Family Medicine specialty.  It has the dollars, it has, Conversion FActors which is also dollars and then what we call RVUs which is just number.  When I pulled it out I could not get it to format correctly.  Will you process work with this example again. 

https://powerusers.microsoft.com/t5/General/Filter-Excel-Table-based-off-of-a-quot-contains-quot-sea...

 

Doesnt like that as the data I believe is coming in as a string?  Can I force it be a value somehow?

 

 

VeeLearnMSFT_0-1652107499397.png

 
 
VeeLearnMSFT_1-1652107678940.png

 

 
formatnumber(outputs('Get_a_row')?['body/25th '], 'C2''en-us')
Expiscornovus
Super User
Super User

Hi @VeeLearnMSFT,

 

Try turning it into a float before you format it.

formatnumber(float(outputs('Get_a_row')?['body/25th ']), 'C2', 'en-us')

 

@Expiscornovus I deleted my original post as I got it to work by getting the list rows present in a table and follwing your directions trying to mimic your example albeit in Sharepoint.  I also missed a crutial step which was the CR, finally it dawned on me that it meant Carriage Return - small detail. Once i put that in there I got it to work.

 

You  helped me with the formatting.  How do I implement the formatting you provided so that it shows dollars using the function you providedl

 

formatnumber(float(outputs('Get_a_row')?['body/25th ']), 'C2', 'en-us')

 

VeeLearnMSFT_0-1652354095799.png

 

VeeLearnMSFT_1-1652354104867.png

VeeLearnMSFT_2-1652354189023.png

 

Here is my output.

I am almost there...

Shujaath_Khan
Super User
Super User

Hi @VeeLearnMSFT 

Just a suggestion wat if we can transpose the format as snip below  and show. That would look legible when shown from PVA as the cards would be vertical in bots and that looks nice.

thanks

shujaathkhan_0-1652354608319.png

 

Expiscornovus
Super User
Super User

Hi @VeeLearnMSFT,

 

In that append to string variable 2 action use expressions for every field to add the formatting for every dollar value. Try this format:

formatnumber(float(item()?['25th ']), 'C2', 'en-us')

 

The original file looks like this.  Because I could not make the filter show all 3 lines:

1. Family Medicine - TCC

2. Family Medicine - TCC per Work RVU

3. Family Medicine - Work RVU

 

I created a separate table that only contains the TCCs for the specialties.  The ideal would be to show all 3 lines for each specialty but I dont know how I would show all 3 and with the proper formatting.  TCC and TCC per RVU need $ formatting while Work RVU does not.  

 

VeeLearnMSFT_0-1652354842290.png

 

If you can kindly walk me through how to create this solution using the transpose if its possible I would definately incorporate that as long as its a clean solution for the user with proper formatting.  I guess I want to make it look as professional and clean as possible.

 

Thanks @Shujaath_Khan 

@Expiscornovus  sorry to bother you again but what is the function for formatting without the dollar signs.  Just want similar formatting but without Dollar.  Thank you

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a free PVA chatbot creation workshop

Register for a free PVA chatbot creation workshop

Learn how to respond rapidly to your customers and employees at scale, using intelligent conversational chatbots.

Users online (4,234)