Extracting Specific Values from a Table and Insert them into New table

[dashmarley11]

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

[jgeddes · Accepted solution]

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 · Accepted solution]

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.

[jgeddes · Accepted solution]

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"

[dashmarley11]

@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?