Skip to main content

Notifications

Announcements

No record found.

Power Platform Community / Forums / General / Format to a table and ...
General
Answered

Format to a table and add dollar values with proper formatting

Posted on by 70

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
    Expiscornovus 1,662 on at
    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
    VeeLearnMSFT 70 on at
    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
    VeeLearnMSFT 70 on at
    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
    Expiscornovus 1,662 on at
    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
    Shujaath_Khan 38 on at
    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
    VeeLearnMSFT 70 on at
    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
    Expiscornovus 1,662 on at
    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
    VeeLearnMSFT 70 on at
    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
    VeeLearnMSFT 70 on at
    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
    Expiscornovus 1,662 on at
    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')

Helpful resources

Quick Links

Exciting News for Copilot Studio Communi…

Get ready to experience a whole new level of engagement with the Copilot Studio…

Celebrating the May Super User of the…

LaurensM is an exceptional contributor to the Power Platform Community…

Check out the Copilot Studio Cookbook…

We are excited to announce our new Copilot Cookbook Gallery in the Community…

Leaderboard

#1
renatoromao Profile Picture

renatoromao 6,459

#2
Pstork1 Profile Picture

Pstork1 1,954

#3
Expiscornovus Profile Picture

Expiscornovus 1,662