Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count words from a list without losing dependencies

I am trying to visualize which competences are associated with each role, based on the frequency of these competences. How do I do this? I am new to PowerBI so I have hard time coming up with a DAX ...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi airielo,

    I am not very clear about your requirement? Did you mean that when you have table like below, you want to calculate the percentage of each language in each role?

    If so, you could try to convert table like below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQqoLMnIz9NRMDewVgjSUTADUsGBPjoKpgZKsTrRSklAJc7KEGmvxLLE4OSizIISiDqYVpDSWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"amount", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "amount", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"amount.1", "amount.2", "amount.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"amount.1", type text}, {"amount.2", type text}, {"amount.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"name"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Int64.Type}})
in
    #"Changed Type2"

    By the way, I don't understand your graph 2, if possible, could you please inform me more detailed information(such as your sample data and your expected output or you could explain the logic to me)

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dax's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

Hi airielo,

I am not clear about your requirement, did you want to get the count of Key competence in each role? If so, you could try to use below measure 

Count comma = LEN(MIN('Table'[Key competence]))-LEN(SUBSTITUTE(MIN('Table'[Key competence]),",",""))+1

Or try below M code to create a custom column like below

List.Count(Text.Split([Key competence],","))

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks Zoe, appreciate your willingness to help!!!

    This is unfortunately not what I was looking for. I am interested to retain both the name of the competency + its count.

    Please see my answer below (with pictures) to see what result I would want to get.

     

    Thanks!

    • dax's avatar
      dax
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi airielo,

      I am not very clear about your requirement? Did you mean that when you have table like below, you want to calculate the percentage of each language in each role?

      If so, you could try to convert table like below

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQqoLMnIz9NRMDewVgjSUTADUsGBPjoKpgZKsTrRSklAJc7KEGmvxLLE4OSizIISiDqYVpDSWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"amount", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "amount", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"amount.1", "amount.2", "amount.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"amount.1", type text}, {"amount.2", type text}, {"amount.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"name"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Int64.Type}})
in
    #"Changed Type2"

      By the way, I don't understand your graph 2, if possible, could you please inform me more detailed information(such as your sample data and your expected output or you could explain the logic to me)

      Best Regards,
      Zoe Zhi

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.