Pivoting single column of data to multiple columns

Power BI
1

image
image703Ă—261 4.33 KB

I have 2 column one is Staffid and second column is degree name. single staff can have multiple degree so i want it represent in different column if more than 1 degree.

1 Like
2

Hello @Shoeb,

This solution is one of many methods that transforms a two column
list of Staff and their Credentials into a table that is Grouped by Staff ID, and
Pivoted into Columns labeled Degree 1, Degree 2, Degree 3. The technique
leverages an Index Column. This method can be applied to both Excel and
Power BI. Solution file is attached.

Staff Credentials Report.pbix (27.6 KB)

Grouped and Pivoted Staff Credentials Table

mcode used in the transform:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjQwVNJRclSK1QGxjdDYTlC2MZK4MZq4M5RtAhGPBQA=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StaffID = _t, Degree = _t]),
#“Changed Type” = Table.TransformColumnTypes(Source,{{“StaffID”, Int64.Type}, {“Degree”, type text}}),
#“Grouped Rows” = Table.Group(#“Changed Type”, {“StaffID”}, {{“Data”, each _, type table [StaffID=nullable number, Degree=nullable text]}}),
#“Added Custom” = Table.AddColumn(#“Grouped Rows”, “Custom”, each Table.AddIndexColumn([Data],“Index”,1)),
#“Removed Columns” = Table.RemoveColumns(#“Added Custom”,{“StaffID”, “Data”}),
#“Expanded Custom” = Table.ExpandTableColumn(#“Removed Columns”, “Custom”, {“StaffID”, “Degree”, “Index”}, {“StaffID”, “Degree”, “Index”}),
#“Changed Type1” = Table.TransformColumnTypes(#“Expanded Custom”,{{“Index”, type text}}),
#“Added Custom1” = Table.AddColumn(#“Changed Type1”, “Custom”, each "Degree "&[Index]),
#“Removed Columns1” = Table.RemoveColumns(#“Added Custom1”,{“Index”}),
#“Pivoted Column” = Table.Pivot(#“Removed Columns1”, List.Distinct(#“Removed Columns1”[Custom]), “Custom”, “Degree”)
in
#“Pivoted Column”

1 Like
3

Thanks a lot. How I can learn and master such techniques.

1 Like
4

Hello @Shoeb,

So you ask how may I skill up when starting out with Power Query and Power BI (and now Fabric)?

The answer is close at hand. Begin your learning path by exploring the resources of EnterpriseDNA.

Additionally, Wyn Hopkins curates a fantastic list of resources on
the following section of his pbi.guide website:

Power BI Resources – Power BI Guide (pbi.guide)

2 Likes