Forum Discussion

Michael_Tsai's avatar
Michael_Tsai
Frequent Visitor
3 years ago
Solved

Find all the rows's value that matching one value in current row (Power Query)

Hi here is my data:

How do I combine the retailer information in each row that have the same phone model in Power Query without group by?

 

Here are some examples that i expect the outcome

 

Thanks in advance!

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi Michael_Tsai 

    let
    Source = your_table,
    groups = 
        Table.Group(
            Source, "Phone model", 
            {{"all", (x) => Table.AddColumn(x, "Used_Retailer", (w) => Text.Combine(x[Retailer], ", "))}, 
            {"Exclusive_Retailer", each if Table.RowCount(Table.Distinct(_)) = 1 then "Y" else "N"}}
        ),
    expand = Table.ExpandTableColumn(groups, "all", {"Retailer", "Used_Retailer"}, {"Retailer", "Used_Retailer"})
in
    expand

1 Reply

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    Hi Michael_Tsai 

    let
    Source = your_table,
    groups = 
        Table.Group(
            Source, "Phone model", 
            {{"all", (x) => Table.AddColumn(x, "Used_Retailer", (w) => Text.Combine(x[Retailer], ", "))}, 
            {"Exclusive_Retailer", each if Table.RowCount(Table.Distinct(_)) = 1 then "Y" else "N"}}
        ),
    expand = Table.ExpandTableColumn(groups, "all", {"Retailer", "Used_Retailer"}, {"Retailer", "Used_Retailer"})
in
    expand