Forum Discussion
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...
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.
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"