Forum Discussion
Extracting Specific Values from a Table and Insert them into New table
I have a table with rates where all of the data is in one row:
| Rate001mo | Rate002mo | Rate003mo | Rate004mo | Rate005mo |
| .065 | .075 | .055 | .040 | .035 |
I want to create a new table that looks like this:
| Term | Rate |
| 1 | .065 |
| 2 | .075 |
| 3 | .055 |
| 4 | .040 |
| 5 | .035 |
That is pretty straightforward in Power Query. The following code should work. Create a blank query and paste this code into the advanced editor, changing the source to equal the query that has your data in the single row.
let Source = NameOfTheQueryThatHasTheData, #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Term", "Rate"), #"Transform Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Term", each Text.TrimStart(Text.Select(_,{"0".."9"}), "0"), type text}}) in #"Transform Text"Unpivoting is a pretty helpful function and can be used on any table in power query.
After the unpivot step the code is extracting the numbers from the column with the header names and removing the leading zeros.
3 Replies
That is pretty straightforward in Power Query. The following code should work. Create a blank query and paste this code into the advanced editor, changing the source to equal the query that has your data in the single row.
let Source = NameOfTheQueryThatHasTheData, #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Term", "Rate"), #"Transform Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Term", each Text.TrimStart(Text.Select(_,{"0".."9"}), "0"), type text}}) in #"Transform Text"jgeddes Thank you! I have no idea how that worked but it does. Can that only be done from a base query, or can that also be accomplished by using a Data Set?
Unpivoting is a pretty helpful function and can be used on any table in power query.
After the unpivot step the code is extracting the numbers from the column with the header names and removing the leading zeros.