Formatting a table from Excel to work with Graphs

[petermb72]

I am getting tables out of excel that look like the following:

Location   Jan   Feb   Mar  April   May   June   July   Aug   Sept   Oct   Nov   Dec

North         2      4       5         3       2        1        4        3       3

South         3      2       3         1       5        4        3        3       1         1      1        1

East            2      1       2          1

So it is multiple locations and multiple months.  I would like to transform the data so I can do visuals on it.  I assume that I want the data in colums like:

Location, Month, total

The people that create these tables do not like to change thier ways and I would like to take what they do in excel and manipulate the data to get the graphs.  I am looking for a basic clustered bar graph that would show the regions grouped together for each month on the axis.  any help on making the data work would be great!

Thanks,

Peter

[Anonymous · Accepted solution]

Load the data into Power Query. Select the first column--> Table-->Transform-->Any Column-->Unpivot Columns-->Unpivot other column

That will get your data into tabular format, which is what you need. Set your data types and name the columns and you should be on your way. 

[petermb72]

AWSOME!!!!  It did just what I needed!

Thank You very much!

Peter

[ChrisMendoza]

@petermb72 -

To get [Location], [Month], & [Value] you could use Power Query as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssvKslQ0lEyAmITIDYFYmMo3xAqZgzFUBSrE60UnF8K1gZTagxVboqmxRAFg3S6JhaXIJkPo7Gj2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Location = _t, Jan = _t, Feb = _t, Mar = _t, April = _t, May = _t, June = _t, July = _t, Aug = _t, Sept = _t, Oct = _t, Nov = _t, Dec = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"Aug", Int64.Type}, {"Sept", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Month", "Value")
in
    #"Unpivoted Other Columns"

Then you should find it easier to visualize.