Pass 1 column to many columns and asign values from a second column

[Padagon]

Hi, I was wondering if someone could help me in Power BI to go from this table, 

Name	Category	Value	Action
ABC	Blue	OK	Go
ABC	Green	Midpoint	Go
EFG	Blue	Midpoint	Done
EFG	Green	NO	Done
GHJ	Blue	OK	Lead
GHJ	Green	NO	Lead

In this situation the Name and the Action is duplicated but having different category and a value for each one. I need to transform the category into columns, and visualize the value for each category as values for Blue and Green new columns

To this:

Name	Blue	Green	Action
ABC	OK	Midpoint	Go
EFG	Midpoint	NO	Done
GHJ	OK	NO	Lead

What I need to do is to visualize the table like this last one, I don't want to group the Name and the Action columns like when using a Matrix.

Thank you.

[danextian · Accepted solution]

Hi @Padagon 

This calls for grouping and pivoting in M. Try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcsopTQVS/t5Awj1fKVYHJuFelJqaB6R9M1MK8jPzShDyrm7uCI1I0i75ealICmAG+Pkjy7l7eKHZ6pOamIIkhaINIhcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t, Value = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Category", type text}, {"Value", type text}, {"Action", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Action"}, {{"Group", each _, type table [Name=nullable text, Category=nullable text, Value=nullable text, Action=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Pivoted", each let t = [Group]
 in 
 
 Table.Pivot(t, List.Distinct(t[Category]), "Category", "Value")),
    #"Expanded Pivoted" = Table.ExpandTableColumn(#"Added Custom", "Pivoted", {"Name", "Blue", "Green"}, {"Name", "Blue", "Green"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Pivoted",{"Group"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Name", type text}, {"Blue", type text}, {"Green", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Name", "Blue", "Green", "Action"})
in
    #"Reordered Columns"

[aduguid · Accepted solution]

I'd use the pivot column feature in Power Query. 
https://learn.microsoft.com/en-us/power-query/pivot-columns

First open Power Query and select the "Transform" tab. Then select the column you want to use as a header and click the "Pivot Column" button.

Then dropdown the "Advanced" option and select the aggregate value.

After that you'll see the pivoted table.

[danextian · Accepted solution]

Hi @Padagon 

This calls for grouping and pivoting in M. Try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcsopTQVS/t5Awj1fKVYHJuFelJqaB6R9M1MK8jPzShDyrm7uCI1I0i75ealICmAG+Pkjy7l7eKHZ6pOamIIkhaINIhcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t, Value = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Category", type text}, {"Value", type text}, {"Action", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Action"}, {{"Group", each _, type table [Name=nullable text, Category=nullable text, Value=nullable text, Action=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Pivoted", each let t = [Group]
 in 
 
 Table.Pivot(t, List.Distinct(t[Category]), "Category", "Value")),
    #"Expanded Pivoted" = Table.ExpandTableColumn(#"Added Custom", "Pivoted", {"Name", "Blue", "Green"}, {"Name", "Blue", "Green"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Pivoted",{"Group"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Name", type text}, {"Blue", type text}, {"Green", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Name", "Blue", "Green", "Action"})
in
    #"Reordered Columns"