Forum Discussion
Helper IConverting Columns to Rows
Hi All,
Seeking your help if there's any way in power bi where I can convert columns to rows. Can this be done using measure or should I create a new table instead?
I have this details as example:
| Months | Sep-19 | Oct-19 | Nov-19 | Dec-19 | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 |
| Sales | $ 300.00 | $ 400.00 | $ 600.00 | $ 200.00 | $ 110.00 | $ 232.00 | $ 105.00 | $ 63.00 | $ 2,301.00 | $ 59.00 | $ 2,000.00 | $ 2,111.00 |
I want this to be converted like this:
| Months | Sales |
| Sep-19 | $ 300.00 |
| Oct-19 | $ 400.00 |
| Nov-19 | $ 600.00 |
| Dec-19 | $ 200.00 |
| Jan-20 | $ 110.00 |
| Feb-20 | $ 232.00 |
| Mar-20 | $ 105.00 |
| Apr-20 | $ 63.00 |
| May-20 | $ 2,301.00 |
| Jun-20 | $ 59.00 |
| Jul-20 | $ 2,000.00 |
| Aug-20 | $ 2,111.00 |
Thank You!
Hi norken20 ,
You don't need to create a new table, on the query editor select all the columns except the first one and then chose the unpivot, that will give two columns attribute (will be the name of each column in this case the dates) and Values (the amounts you have for each of the months).
Be aware that if your datasource will increase in the the number of columns that need to unpivot the query will break at refresh, in that case maybe it's better to redo your datasource.
Hi,
try this
create a blank query in PowerQuery and copy below.
Spoilerlet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1W0lFSUFFABcYGBnoGBlhkTHBImOHSAARGuOUMDXFJGRkbYcgYGpjitATkBmNMWSMdYwNDnJpMLbFJGekYYHWxkY6hIdis2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Months = _t, #" Sep-19 " = _t, #" Oct-19 " = _t, #" Nov-19 " = _t, #" Dec-19 " = _t, #" Jan-20 " = _t, #" Feb-20 " = _t, #" Mar-20 " = _t, #" Apr-20 " = _t, #" May-20 " = _t, #" Jun-20 " = _t, #" Jul-20 " = _t, #" Aug-20 " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Months", type text}, {" Sep-19 ", type text}, {" Oct-19 ", type text}, {" Nov-19 ", type text}, {" Dec-19 ", type text}, {" Jan-20 ", type text}, {" Feb-20 ", type text}, {" Mar-20 ", type text}, {" Apr-20 ", type text}, {" May-20 ", type text}, {" Jun-20 ", type text}, {" Jul-20 ", type text}, {" Aug-20 ", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Months"}, "Attribute", "Value"),
#"Extracted Last Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", each Text.End(_, 10), type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Extracted Last Characters",{{"Value", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",",","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Value", type number}})
in
#"Changed Type1"or:
mark "Months" column and press "Unpivot other columns"
/Adam
4 Replies
- MFelix
Super User
Hi norken20 ,
You don't need to create a new table, on the query editor select all the columns except the first one and then chose the unpivot, that will give two columns attribute (will be the name of each column in this case the dates) and Values (the amounts you have for each of the months).
Be aware that if your datasource will increase in the the number of columns that need to unpivot the query will break at refresh, in that case maybe it's better to redo your datasource.
norken20Thank you so much!
Hi,
try this
create a blank query in PowerQuery and copy below.
Spoilerlet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1W0lFSUFFABcYGBnoGBlhkTHBImOHSAARGuOUMDXFJGRkbYcgYGpjitATkBmNMWSMdYwNDnJpMLbFJGekYYHWxkY6hIdis2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Months = _t, #" Sep-19 " = _t, #" Oct-19 " = _t, #" Nov-19 " = _t, #" Dec-19 " = _t, #" Jan-20 " = _t, #" Feb-20 " = _t, #" Mar-20 " = _t, #" Apr-20 " = _t, #" May-20 " = _t, #" Jun-20 " = _t, #" Jul-20 " = _t, #" Aug-20 " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Months", type text}, {" Sep-19 ", type text}, {" Oct-19 ", type text}, {" Nov-19 ", type text}, {" Dec-19 ", type text}, {" Jan-20 ", type text}, {" Feb-20 ", type text}, {" Mar-20 ", type text}, {" Apr-20 ", type text}, {" May-20 ", type text}, {" Jun-20 ", type text}, {" Jul-20 ", type text}, {" Aug-20 ", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Months"}, "Attribute", "Value"),
#"Extracted Last Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", each Text.End(_, 10), type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Extracted Last Characters",{{"Value", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",",","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Value", type number}})
in
#"Changed Type1"or:
mark "Months" column and press "Unpivot other columns"
/Adam
