Converting Columns to Rows

[norken20]

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!

[MFelix · Accepted solution]

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.

[Mariusz]

Hi @norken20 

You can use transpose in power query as on the attached.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Adamtall · Accepted solution]

Hi, 

try this 

create a blank query in PowerQuery and copy below.

Spoiler

let
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

[MFelix · Accepted solution]

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.

[norken20]

Thank you so much!