Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

How to combine rows based on matching values in one column and merge values in a different column

I suspect that even if there is an answer to this question I won’t have the skill set yet to implement, but here goes anyway.

 

I need to display whether applications in a list are available to install for Windows PC, Mac, or both.  Currently I use a PC-Mac column to display “Windows PC” or “Mac” which adds a second row for apps that can be installed on both (First screenshot in attached png).

 

What I would like to do instead is remove those second rows and either:

 

Combine “Windows PC” and “Mac” into the same cell (e.g. “Windows PC, Mac”), 

or,

At the beginning of the list, add a “Windows PC” column and a “Mac” column and have an “x” entered in the respective columns if the app can be installed (Second screenshot in attached png).

 

Thanks for any help!

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    Solution for first approach

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrPzEvJLy9WCHBWitWJVnLCFHLGFHLBLuSbmAxmu2JKuyJJu2FKu2MXgunwQJOOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"PC-Mac" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Application"}, {{"Platform", each Text.Combine([#"PC-Mac"],", "), type nullable text}})
in
    #"Grouped Rows"

    Solution for 2nd approach

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrPzEvJLy9WCHBWitWJVnICCvkmJoPZzpjSLtiFYDpckdhumErdsQvBdHhgSnvCpGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"PC-Mac" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "X"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"PC-Mac"]), "PC-Mac", "Custom"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Windows PC", "Mac", "Application"})
in
    #"Reordered Columns"

     

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    Solution for first approach

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrPzEvJLy9WCHBWitWJVnLCFHLGFHLBLuSbmAxmu2JKuyJJu2FKu2MXgunwQJOOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"PC-Mac" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Application"}, {{"Platform", each Text.Combine([#"PC-Mac"],", "), type nullable text}})
in
    #"Grouped Rows"

    Solution for 2nd approach

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrPzEvJLy9WCHBWitWJVnICCvkmJoPZzpjSLtiFYDpckdhumErdsQvBdHhgSnvCpGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"PC-Mac" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "X"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"PC-Mac"]), "PC-Mac", "Custom"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Windows PC", "Mac", "Application"})
in
    #"Reordered Columns"