Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 68NccebruZFLj4Mzk+dnFc
Copilot Studio - General
Answered

Format to a table and add dollar values with proper formatting

Like (0) ShareShare
ReportReport
Posted on 9 May 2022 11:26:01 by 508

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?

 

 

Categories:
  • Expiscornovus Profile Picture
    31,665 Most Valuable Professional on 15 May 2022 at 15:34:21
    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 Profile Picture
    508 on 15 May 2022 at 13:43:22
    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 Profile Picture
    508 on 12 May 2022 at 11:30:35
    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.  

     

    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 

  • Verified answer
    Expiscornovus Profile Picture
    31,665 Most Valuable Professional on 12 May 2022 at 11:27:10
    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 Profile Picture
    1,111 Super User 2024 Season 1 on 12 May 2022 at 11:23:59
    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

    shujaathkhan_0-1652354608319.png

     

  • VeeLearnMSFT Profile Picture
    508 on 11 May 2022 at 19:58:28
    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')

     

    VeeLearnMSFT_0-1652354095799.png

     

    VeeLearnMSFT_1-1652354104867.png

    VeeLearnMSFT_2-1652354189023.png

     

    Here is my output.

    I am almost there...

  • Verified answer
    Expiscornovus Profile Picture
    31,665 Most Valuable Professional on 09 May 2022 at 14:50:17
    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 Profile Picture
    508 on 09 May 2022 at 14:48:34
    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?

     

     

    VeeLearnMSFT_0-1652107499397.png

     
     
    VeeLearnMSFT_1-1652107678940.png

     

     
    formatnumber(outputs('Get_a_row')?['body/25th '], 'C2''en-us')
  • VeeLearnMSFT Profile Picture
    508 on 09 May 2022 at 12:40:49
    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 Profile Picture
    31,665 Most Valuable Professional on 09 May 2022 at 12:11:04
    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')

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Copilot Studio - General

#1
Ekta Gupta Profile Picture

Ekta Gupta 4

#2
ricardodesouza Profile Picture

ricardodesouza 2

#2
L-1234567-0 Profile Picture

L-1234567-0 2

Overall leaderboard