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 giv...
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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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"