Answered

Format to a table and add dollar values with proper formatting

(0) Share

Report

Posted on by VeeLearnMSFT

506

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

PVA Output:

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

Categories:

General

All responses (10)

Answers (2)

Expiscornovus 29,370 on at

Like (1)

Report
Re: Format to a table and add dollar values with proper formatting

Hi @VeeLearnMSFT,

Without a currency it could be something like below:

formatnumber(float(item()?['25th ']), '0,0.00', 'en-us')
VeeLearnMSFT 506 on at

Like (0)

Report

Re: Format to a table and add dollar values with proper formatting

@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
VeeLearnMSFT 506 on at

Like (0)

Report

Re: Format to a table and add dollar values with proper formatting

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.

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
Verified answer

Expiscornovus 29,370 on at

Like (0)

Report
Re: Format to a table and add dollar values with proper formatting

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')
Shujaath_Khan 1,111 on at

Like (0)

Report

Re: Format to a table and add dollar values with proper formatting

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
VeeLearnMSFT 506 on at

Like (0)

Report
Re: Format to a table and add dollar values with proper formatting

@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')

Here is my output.
I am almost there...
Verified answer

Expiscornovus 29,370 on at

Like (1)

Report
Re: Format to a table and add dollar values with proper formatting

Hi @VeeLearnMSFT,

Try turning it into a float before you format it.

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

Like (0)

Report

Re: Format to a table and add dollar values with proper formatting

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

formatnumber(outputs('Get_a_row')?['body/25th '], 'C2', 'en-us')
VeeLearnMSFT 506 on at

Like (0)

Report

Re: Format to a table and add dollar values with proper formatting

@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-search-and/m-p/1575764#M3673
Expiscornovus 29,370 on at

Like (1)

Report
Re: Format to a table and add dollar values with proper formatting

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-table-for-the-bot-response/ba-p/1190942

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')