Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Unpivot /Organizing Data

Good day, Power BI Community!

 

I have been struggling to find a way to get the below structured data into table form in my model. I've worked with unpivoting data before, but this structure is giving me some difficulty. Any thoughts or suggestions other than manually organizing in Excel?

 

Original data source:

https://ucr.fbi.gov/crime-in-the-u.s/2019/crime-in-the-u.s.-2019/topic-pages/tables/table-18

 

Thank you!

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    I have done this in Power Query in MS Excel.  Please follow these steps in PowerBI Desktop as well.  You may download my solution workbook from here.

    Hope this helps.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    I have done this in Power Query in MS Excel.  Please follow these steps in PowerBI Desktop as well.  You may download my solution workbook from here.

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you, Ashish. This is fantastic. I greatly appreciate the guidance here. 

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    The file is in .xls format which cannot be opened in the Power BI service. You will (at a minimum) need to download the .xls and convert it to .xlsx before proceeding.

     

    Then I would propose to have a manual column name mapping table called "Headers".

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZHBboMwDIZfBXHupMLarjuuu65S1cMubQ8umIAUbOQklXj7JZoqsUAOu/J//0dsXy75J2vXU5Gv8hMPToPtmDIl7Ib8tnrG5d9YgBRO4lcff3eskWxWSddjxk2DZNBMoM0MErBTy9YDRyc1SgZUX4mYCJXuVGhcqQcyGpxqrQcW/Lt/1KM/v/nqGYbFZ++fWdR5D9/5fkcZl2rFepJH1SJs+0MpgYcPav84MAactouiMgXH1nCFk/CAYsf0GYrNHItN4RIHJ0pDYrjdFIjbYZtfIBXS+GJbbJbH2s+o2BM2fGTLkj2w7SqNfvakr1wn6V/v7Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}})
in
    #"Changed Type"

     

     

     

    which would finally allow you to run your unpivot

     

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\table-18.xlsx"), null, true),
    #"19tbl18_Sheet" = Source{[Item="19tbl18",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"19tbl18_Sheet",5),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",3),
    #"Filled Down" = Table.FillDown(#"Removed Bottom Rows",{"Column1", "Column2"}),
    NewHeaders = Table.RenameColumns(#"Filled Down",List.Zip({Headers[Column1],Headers[Column2]})),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(NewHeaders, {"Population group", "Population range"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
    #"Changed Type"