Skip to main content

Notifications

Power Apps - Power Query
Answered

Transpose formula

(0) ShareShare
ReportReport
Posted on by 233
Hi,
 
Below is a collection structure (colData)
 
Account Act Parameter FY November December
A0000001 M1 Target FTEs FY 24-25 10 NA
A0000001 M2 Actual FTEs FY 24-25 8 NA
A0000001 A1 FTE Target Status FY 24-25 Not Met Met
 
ClearCollect(colGroupedData,AddColumns(GroupBy(colData,Account,Parameter,Act,FY,GroupedItems),UOM,First(GroupedItems).UOM,November,IfError(Sum(GroupedItems,November),""),December,IfError(Sum(GroupedItems,December),"")))
 
used the above formula to generate the result in a gallery -
 
Parameter November December
Target FTEs 10  
Actual FTEs 8  
FTE Target Status    
 
But the actual requirement is like the one below. Unable to trace where i am making mistake in the formula. Kindly help.
 
Parameter November December
Target FTEs 10 NA
Actual FTEs 8 NA
FTE Target Status Not Met Met
 
Regards
Categories:
  • Sam70 Profile Picture
    Sam70 233 on at
    Transpose formula
     
    Made a change in the input source, used your first response and was able to get the desired output.
     
    Thanks and Regards
  • WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Transpose formula
    A quick follow-up to see if you received the answer you were looking for or if you need further assistance.

    Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Transpose formula
    The fundamental issue is the field type for November and December - I assume it is Text otherwise you would not be able to add the values of NA. Met and Not Met. Your example data is not really helping to understand what you are doing as the original and desired output both have three records and therefore GroupBy() seems redundant. Is this what you are trying to do ?
    ClearCollect(
       colGroupedData,
       AddColumns(
          GroupBy(
             colData,
             Account,
             Parameter,
             Act,
             FY,
             GroupedItems
          ),
          UOM,
          First(GroupedItems).UOM,
          November,
          IfError(
             Sum(GroupedItems,Value(November)),
            November
          ),
          December,
          IfError(
             Sum(GroupedItems,Value(December)),
             December
          )
       )
    )
    I still do not know what UOM is at it does not seem to appear in your desired output.
     
    Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee
  • Sam70 Profile Picture
    Sam70 233 on at
    Transpose formula
     
    In the last line, FTE Target Status is a Parameter value. Since for the month of November, the Target FTE was 10 and Actual came out to be 8, hence the Status is NOT Met. For December its NA for Target and NA for Actual.
    Through the formula i was able to get the figures, 10 and 8 but not the character values NA, Not Met and Met. Hence the need for help.
     
    Regards
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 145,580 on at
    Transpose formula
    Hi @Sam70
    The NA parts are easy enough, but the logic for the last line needs more understanding (if at all possible) - also not sure what UOM is.
    ClearCollect(
       colGroupedData,
       AddColumns(
          GroupBy(
             colData,
             Account,
             Parameter,
             Act,
             FY,
             GroupedItems
          ),
          UOM,
          First(GroupedItems).UOM,
          November,
          IfError(
             Sum(GroupedItems,November),
             "NA"
          ),
          December,
          IfError(
             Sum(GroupedItems,December),
             "NA"
          )
       )
    )
     
    Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    LinkedIn    Buy me a coffee

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

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,580

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,909

Leaderboard

Featured topics