Attempt 3 - How to avoid duplicates and get totals to match when transforming and aligning

Question

HELP.  I can't seem to transform and align my data without creating numerous (dozens) of duplicates and totals that don't match the starting number.

I've tried a few times with notional data.  The help so far is greatly appreciated, but neither approach seems to translate to the real data.  So, here's a sample of the real data. YXDB attached   My goal seems very simple.  I want 1 column for text and 1 column for dollars for each period (2019,2020, 2021 B, and 2021 O).   There may not be text or dollars associated with each period.

Unless there is a clearer solution, I am trying to structure the data along the lines of the table below.  THANKS

LINEPROJECTPROGRAM PROGRAM DESCPPERIODTEXTAMMOUNT    2019      2020      2021 A      2021 B

Sample_Problem_R.yxdb

cplewis90 · Accepted Answer

Hello @hellyars,

Not sure if this meets your needs exactly, but figured I would give it a shot. here is a snippet of the results of the first couple records:

DiegoParker · Accepted Answer

Hi @hellyars,

This is my approach. I basically added the year+value columns together for each year, then I did my transpose and then text to columns. Hope that helps.

Please find the workflow attached.

Best,

Diego