Unpivot /Organizing Data

[Anonymous]

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!

[Ashish_Mathur · Accepted solution]

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]

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

[Ashish_Mathur]

You are welcome.

[lbendlin]

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"