Forum Discussion

petermb72's avatar
petermb72
Icon for Helper IV rankHelper IV
7 years ago
Solved

Formatting a table from Excel to work with Graphs

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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. 

3 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      petermb72
      Icon for Helper IV rankHelper IV
      7 years ago

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

       

      Thank You very much!

       

      Peter