Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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"
Proud to be a Super User! | |
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.
Proud to be a Super User! | |
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.
Proud to be a Super User! | |
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"
Proud to be a Super User! | |
@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?
User | Count |
---|---|
96 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |