Conditional column in Power Query

Question

I have a table with 3 columns: Name, Property and Value

All names are unique. But there are cases where, for instance, for different Names , both their properties and values are equal at the same time.

I want to add conditional column that would add all Names with line feed delimiter for which properties and values are equal. So for example, for 1st Name I would go to conditional column and would see list of 5 other names that have the same property and value

So far I have tried adding conditional column:

If Property equals Property Then

Else if Value equals Value Then Name

but it just returns values from name column and I dont know to add up these names together

Thanks!

hsan · Accepted Answer · 2019-05-02 13:32:37Z

You could group your rows by Property and Value, then combine the Name of for each row.

= Table.Group(Source, {"Property", "Value"}, {{"Names", each Text.Combine(_[Name], ", "), type text}})

Table.Group - like SQL's GROUP BY
Text.Combine - like array joining in other languages, you provide a list and a separator and receive a string

Original table:

| Name | Property | Value |
| ---- | -------- | ----- |
| A    | a        |     1 |
| B    | b        |     2 |
| C    | a        |     2 |
| D    | a        |     1 |
| E    | b        |     2 |

Full query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Group(Source, {"Property", "Value"}, {{"Names", each Text.Combine(_[Name], ", "), type text}})
in
    Grouped

Result:

| Property | Value | Names |
| -------- | ----- | ----- |
| a        |     1 | A, D  |
| b        |     2 | B, E  |
| a        |     2 | C     |

Collectives™ on Stack Overflow

Conditional column in Power Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related