Transpose formula

(0) Share

Report

Posted on by Sam70

271

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:

Power Query

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

WarrenBelz 153,411 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Sam70 271 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

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

Was this reply helpful? Yes No
WarrenBelz 153,411 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@Sam70,

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

Was this reply helpful? Yes No
WarrenBelz 153,411 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@Sam70,

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

Was this reply helpful? Yes No
Sam70 271 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

Made a change in the input source, used your first response and was able to get the desired output.

Thanks and Regards

Was this reply helpful? Yes No