data transformation assistance for a PowerBI newbie

[cm008507]

Hi there,

I am very new to PowerBi Desktop and struggling with transforming a Excel data source.  

I'm attempting to transform data from an Excel file but can't seem to pivot it so all of the demensions are columns and where the levels are not repeatingfor each worker tpye (ie. Permanent, Contractor, Temporary, etc.).   

Sample source data showing the first 3 columns is below and the monthly date value columns continue to the right into the current calendar year.    

Permanent	Nov-13	Dec-13
Level 5  - Report Only	2	10
Level 4  - First Aid	2	3
Level 3 - Medical Treatment	0	1
Level 3 - Restricted Work	0	0
Level 2 - Lost Time	1	1
Non-occupational
Refinery Temporary	Nov-13	Dec-13
Level 5 - Report Only	0	0
Level 4  - First Aid	0	0
Level 3 - Medical Treatment	0	0
Level 3 - Restricted Work	0	0
Level 2  - Lost Time	0	0
Non-occupational
Contractor	Nov-13	Dec-13
Level 5  - Report Only	0	1
Level 4 - First Aid	1	2
Level 3 - Medical Treatment	0	0
Level 3 - Restricted Work	0	0
Level 2  - Lost Time	0	1
Non-occupational
Total all Injuries, all workers	6	19
Refinery Employee	Nov-13	Dec-13
Recordable (Level 3 & 2)	1	2
Employee Hours	Nov-13	Dec-13
Hours	160547	167659
EE instant TRIF	1.25	2.39
Rolling EE TRIF
YTD EE TRIF	1.25	1.83
Contractor	Nov-13	Dec-13
Level 3+	0	1

I believe I need to get the data into this format...

Employee Type	Severity	11/1/2013	12/1/2013	1/1/2014	2/1/2014	3/1/2014
Permanent	Level 5
	Level 4
	Level 3
	Level 2
	Level 1
	Non-occupational
Temporary	Level 5
	Level 4
	Level 3
	Level 2
	Level 1
	Non-occupational
Contractor	Level 5
	Level 4
	Level 3
	Level 2
	Level 1
	Non-occupational

I use a separate table to store the exposure hours which are needed to calcualte monthly TRIF metric.

The applied steps done so far include a 'Navigation' step to get at the embedded table data and 'Filter Rows' step to remove the balnk rows.  From there I tried copying column 1 to use it to move the Severity (ie. Levels) and then have column 1 only listing the 'worker type' values, (ie. Permanent, Temporary, Contractor).  I then filtered out the workker type values out of the new column.  Then proceeded to filter out the 'Severity' values out of column 1 but the filering of the new column also filtered column 1.   Thought this would happen using a copy of column.  😞 

Also attempted to pivot the data and then adress the repeating severity levels but hit a wall there as well.  

Any assistance is greatly appreciated and I look forward to the day when I am more skilled with PowerBI and can look back and laugh at myself for my early struggles.  🙂

[Anonymous]

Hi @cm008507 ,

Did @jbwtp 's method help you?

If you problem has been solved, please mark the reply as a solution.

More people will benefit from it.

Best Regards,

Stephen Tao

[jbwtp]

Hi @cm008507,

Do you mean somthing like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZFNCsIwEIWvErq2YLUeQBRX/lEKLoqLkI4QbDMyjoXexrN4MtOoGCIU3QgJDJmPN5n3iiKKBu7sB0W0BaqlAcP2YY1NnIxtMQfVFV1/CQ1UYnK7ilhkcEJisTFVa6GRvcnQg1IHLTSdWUx1+UR8mbHtr6DUSlYiJ5BcPwYPO6mAy+DMpBVDKXZIxyflzxtZaol2WK5rcAovlTWaGJW6nCRrNLLy9vXKDA7aALUih9ouJqnt9+DDgfBDnwaERJ8BIfeFAW6eb8Gb+M6CGRomqRjp5/jDzNJg9cTl/9/V+9Pf3wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Employee Type", each if Text.Contains([Column1], "-") then null else [Column1], type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Severity", each if Text.Contains([Column1], "-") then  Text.BeforeDelimiter([Column1], " - ") else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Employee Type"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"Column5", type text}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Permanent", type text}, {"Nov-13", type text}, {"Dec-13", type text}, {"Permanent_1", type text}, {"Column5", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Column5] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Permanent"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Permanent_1", "Column5", "Nov-13", "Dec-13"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Permanent_1", "Emplyee Type"}, {"Column5", "Severity"}})
in
    #"Renamed Columns"

[Copy and paste into a Blank Query in PQ editor]

Kind regards,

John

[cm008507]

Thank-you for your assistance

[lbendlin · Accepted solution]

Instead of pivoting you need to do the opposite - unpivot your source data.  The pivoting will happen automatically when you display yor data in the PowerBI visuals (for example the matrix visual)

The format you want to end up with is 

Employee type|Severity|Date|Count