Forum Discussion

NPHOMER's avatar
NPHOMER
New Member
3 years ago
Solved

Two columns connection

Hello guys

 

First time here! Thanks in advance. Really need some help with a basic two columns connection.

 

Here is an example of what I need. I have these two columns, the names of people that are project owners:

 

Now I need a visual (simple Stacked bar chart) with how many projects each people participated in. Something like this:

 

 

(done quickly in power point just to show what I need)

 

I tried to merge columns but didn't work.

 

Can you help me? Thanks again

 

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi NPHOMER ,

    Please try below steps:

    1. below is my test table

    Table:

     

    2. unpivoted table and filter value in Power Query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJR8srPyANSSrE6CHEjoIBvYlElurgxDnETHOaYAgUCEktzYNqQ5cwQesBKkOXMcdhjga4nFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Owner 1" = _t, #"Project Owner 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project Owner 1", type text}, {"Project Owner 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Project Owner"}, {"Value", "Name"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Name] <> ""))
in
    #"Filtered Rows"

     

    3. add a Clustered bar chart with fields

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi NPHOMER ,

    Please try below steps:

    1. below is my test table

    Table:

     

    2. unpivoted table and filter value in Power Query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJR8srPyANSSrE6CHEjoIBvYlElurgxDnETHOaYAgUCEktzYNqQ5cwQesBKkOXMcdhjga4nFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Owner 1" = _t, #"Project Owner 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project Owner 1", type text}, {"Project Owner 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Project Owner"}, {"Value", "Name"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Name] <> ""))
in
    #"Filtered Rows"

     

    3. add a Clustered bar chart with fields

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Nithinr's avatar
    Nithinr
    Resolver III
    3 years ago

    Have you tried unpivoting the projectowner 1 and project owner 2 columns