Forum Discussion
Pivot Columns With Different Attributes
Hi all,
I am not sure if this is the right place to put this, but I am currently in the process of using Power Query to try and pivot several columns.
Currently my data format looks like the following:
| Name | Jobs | Role ID | Total Hours Worked |
| Sophie Thomson | Cook | 1 | 20 |
| Sophie Thomson | Singer | 2 | 10 |
| Joe Bloggs | Artist | 3 | 23 |
| Joe Bloggs | Food Critic | 4 | 34 |
| Joe Bloggs | TV Star | 5 | 56 |
| Alix Max | Actor | 6 | 45 |
| Max Sample | Chef | 7 | 23 |
| Alix Max | Artist | 3 | 12 |
| Max Sample | Food Critic | 4 | 12 |
I am hoping to have an output (that I can preferably export to Excel; as the specification is that it is delivered in Excel Format) that looks a bit like this:
| Name | Job 1 | Job 1 Hours | Job 2 | Job 2 Hours | Job 3 | Job 3 Hours |
| Sophie Thomson | Cook | 20 | Singer | 10 | ||
| Joe Bloggs | Artist | 23 | Food Critic | 34 | TV Star | 56 |
| Alix Max | Actor | 45 | Actor | 12 | ||
| Max Sample | Chef | 23 | Food Critic | 12 |
I have over 150 different types of jobs in the data format above, and have tried to create merge queries and then pivot them, however, I get lots of names which end up blank.
What would be the best way to approach this so I can achieve the format in the second table?
Thanks in advance.
Hi Anonymous ,
According to your description, you could merge and combine columns in powerbi query by the following steps:
Step 1:Merge two columns with “,” (“Jobs” and “Total Hours Worked” column)
Step2: Remove “Role ID” Column
Step3: Write expression
= Table.Group( #"Removed Columns", {"Name"}, {{"Job", each Text.Combine([Merged],","), type text}})Step4: Split Column with “,”
Step5: Rename Columns
The final output is shown below:
In addition: attach m language
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7DsMgDEV/BTFnaJ6d00gdKnUi6hJlQCkFVBJHwJDPr50OjUQHy4Z7bF8PAxewGqtYb2AOsPCMdwBvTDlGceJj9gcRdtHKE0DgF7qBYhcHWgf8a320IWJRElSmwBXgyTpvo53wVRFZpVT/YCJKWlRTNDvROruxu9xozRSB1IZm1LuKAhNyXp2iU4x6YTr/TBybjx7zIu1OPRI1fgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Jobs = _t, #"Role ID" = _t, #"Total Hours Worked" = _t]), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Total Hours Worked", type text}}, "en-001"),{"Jobs", "Total Hours Worked"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Role ID"}), Custom1 = Table.Group( #"Removed Columns", {"Name"}, {{"Job", each Text.Combine([Merged],","), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Job", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Job.1", "Job.2", "Job.3", "Job.4", "Job.5", "Job.6"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name", type text}, {"Job.1", type text}, {"Job.2", Int64.Type}, {"Job.3", type text}, {"Job.4", Int64.Type}, {"Job.5", type text}, {"Job.6", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Job.2", "Job 1 Hours"}, {"Job.3", "Job 2"}, {"Job.4", "Job 2 Hours"}, {"Job.5", "Job 3"}, {"Job.1", "Job 1"}, {"Job.6", "Job 3 Hours"}}) in #"Renamed Columns"Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Anonymous , refer this blog, this might help
https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/
v-yalanwu-msftCommunity Support
Hi Anonymous ,
According to your description, you could merge and combine columns in powerbi query by the following steps:
Step 1:Merge two columns with “,” (“Jobs” and “Total Hours Worked” column)
Step2: Remove “Role ID” Column
Step3: Write expression
= Table.Group( #"Removed Columns", {"Name"}, {{"Job", each Text.Combine([Merged],","), type text}})Step4: Split Column with “,”
Step5: Rename Columns
The final output is shown below:
In addition: attach m language
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7DsMgDEV/BTFnaJ6d00gdKnUi6hJlQCkFVBJHwJDPr50OjUQHy4Z7bF8PAxewGqtYb2AOsPCMdwBvTDlGceJj9gcRdtHKE0DgF7qBYhcHWgf8a320IWJRElSmwBXgyTpvo53wVRFZpVT/YCJKWlRTNDvROruxu9xozRSB1IZm1LuKAhNyXp2iU4x6YTr/TBybjx7zIu1OPRI1fgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Jobs = _t, #"Role ID" = _t, #"Total Hours Worked" = _t]), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Total Hours Worked", type text}}, "en-001"),{"Jobs", "Total Hours Worked"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Role ID"}), Custom1 = Table.Group( #"Removed Columns", {"Name"}, {{"Job", each Text.Combine([Merged],","), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Job", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Job.1", "Job.2", "Job.3", "Job.4", "Job.5", "Job.6"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name", type text}, {"Job.1", type text}, {"Job.2", Int64.Type}, {"Job.3", type text}, {"Job.4", Int64.Type}, {"Job.5", type text}, {"Job.6", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Job.2", "Job 1 Hours"}, {"Job.3", "Job 2"}, {"Job.4", "Job 2 Hours"}, {"Job.5", "Job 3"}, {"Job.1", "Job 1"}, {"Job.6", "Job 3 Hours"}}) in #"Renamed Columns"Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
