The Excel spreadsheet is in Pivot Table form and must be Unpivoted.
The default Transformer for unpivoting data is AttributeExploder
There are a few variations on using it that revolve around workspace performance tuning, but the simplest, although slower performing, way is:
- Exploded Attribute = COUNTY_CD
- Exploded Value = COST_PER_SQFT
- Exploding Type = Features
- Keep Attributes = Yes
This will create new features with the two new unpivoted attributes
For newer versions of FME can make use of the extra Parameter available “ Ignore Attributes Containing” and set this to ia-z] . It seems in this particular case, the only attributes want to unpivot only contain Capital Letters, so for this particular example, this will work.
Otherwise, to filter just the Features from the AttributeExploder Output that contain COST_PER_SQFT values, then send the result to Tester with Test Operator COUNTY_CD Contains Regex ^ A-Z]{3}$
I would note that it would be nice yet still in future FME versions if AttributeExploder used an extra “Pivot Attributes” parameter like the equivalent function in Power BI M langauge. Would make life so much easier to pick attributes that want to keep in the output as the Pivot columns, and which of the remaining Attributes to Explode. For the moment though, variations on an extra Tester are the main ways to filter the AttributeExploder results down to just the Excel spreadsheet cells that want to Explode.
https://learn.microsoft.com/en-us/powerquery-m/table-unpivot
Hi Bwn,
Thank you for the quick reply and followed your guidelines and was able to get the data loaded correctly. However, I wanted to let you know I had used FME 2024 version and did not have the capability of ignoring attributes with regex within the Attribute Exploder, I have only options for case sensitive and case insensitive. Hence, I had to use tester like you mentioned.
Thank you again!
lsugumar.
