Forum Discussion
Unpivot Multiple Columns and Make One Repeated
I have a query where I"m taking a table (RatesData) that's formatted like this:
| EffectiveDate | Rate_001mo | Rate_002mo | Rate_003mo |
| 4/6/2024 | .053 | .054 | .055 |
| 5/7/2024 | .051 | .052 | .053 |
I currently have a query that unpivots the table:
= Table.UnpivotOtherColumns(#"SourceData", {}, "Term", "Rate")
Results:
| Term | Rate |
| EffectiveDate | 4/6/2024 |
| Rate_001 | .043 |
| Rate_002 | .044 |
| Rate_003 | .045 |
| EffectiveDate | 5/7/2024 |
| Rate_001 | .051 |
| Rate_002 | .052 |
| Rate_003 | .053 |
I would like to make the "EffectiveDate" it's own column and then repeat the date for those corresponding rates like this:
| EffectiveDate | Term | Rate |
| 4/6/2024 | Rate_001 | .043 |
| 4/6/2024 | Rate_002 | .044 |
| 4/6/2024 | Rate_003 | .045 |
| 5/7/2024 | Rate_001 | .051 |
| 5/7/2024 | Rate_002 | .052 |
| 5/7/2024 | Rate_003 | .053 |
Anonymous if you click on column "EffectiveDate", go to Transform > arrow beside Unpivot columns > unpivot other columns, it will give you 3 columns:
1) EffectiveDate
2) Attribute > rename to "Term"
3) Value > rename to "Rate"
Cheers,
Nemanja Andic
