Custom column in Power Query to check groupings of rows

[MichaelHutchens]

Hi team, I'm hoping someone might be able to help.

I have a table showing various files ("URL" column), some with labels ("Label" column), and some without. Some files have multiple labels, and there are sometimes multiple rows for each file.

I need to create a custom column in Power Query that looks through each grouping of URLs and tells me whether the grouping contains a label or not. Here's an example of the output I need:

URL	Label	Contains Label
File1	Lable.Archived	Yes
File2	Label.Customers	Yes
File2	Label.Active	Yes
File3		Yes
File3	Label.Staff	Yes
File3		Yes
File4		No
File4		No
File4		No
File5		No

Any help would really be appreciated 🙂

[v-yangliu-msft · Accepted solution]

Hi  @MichaelHutchens ,

You can also use the dax function to.

Here are the steps you can follow：

1. Create calculated column.

Contains Label =
var _count=
COUNTX(FILTER(ALL('Table'),'Table'[URL]=EARLIER('Table'[URL])&&'Table'[Label]<>BLANK()),[Label])
return
IF(
    _count>=1,"Yes","No")

2. Result:

Best Regards,

Liu Yang

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

[v-yangliu-msft · Accepted solution]

Hi  @MichaelHutchens ,

You can also use the dax function to.

Here are the steps you can follow：

1. Create calculated column.

Contains Label =
var _count=
COUNTX(FILTER(ALL('Table'),'Table'[URL]=EARLIER('Table'[URL])&&'Table'[Label]<>BLANK()),[Label])
return
IF(
    _count>=1,"Yes","No")

2. Result:

Best Regards,

Liu Yang

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

[MichaelHutchens · Accepted solution]

Thanks so much @v-yangliu-msft , that solutions works just fine 🙂 Appreciate your time!

[MichaelHutchens]

Hi @amitchandak , thanks so much for responding. I'm getting an error with that code:

[amitchandak]

@MichaelHutchens , Try a new column in power query

let
_col = [URL],
_table = Table.SelectRows(Ranges, each [URL] = _col and [Label] <> null ),
_count = if Table.RowCount(_table[Label]) >=1 then "Yes" else "No"
in
_count