Filtering a table based on values within a nested table

[anktaggrwl]

I have an events object that I'm querying on salesforce that has the following format:

Id	Business_Unit__c	RecordType
ABC123	XYZ	Value
ABC124	SYW	Value
ABC125	SYW	Value
ABC126	XYZ	Value
ABC126	XYZ	Value

I'd like to only bring back rows where EITHER [Business_Unit__c] = "XYZ" or the [Name] field within the record type value table = "XYZ".

The first filter of the 'either' statement is pretty straightforward:

= Table.SelectRows(Source{[Name="Event"]}[Data], each [Business_Unit__c]="XYZ")

But how would I pass an OR to be a lookup based on the name of the value in the RecordType Column, so that I bring back those lines where [Name] within "Value" is also XYZ (since there a number of lines where [Business_Unit__c]<>"XYZ" but the Name field within RecordType DOES equal "XYZ" that I'm currently missing)?

[ReportRanger]

Hi @anktaggrwl were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls data directly from the SF objects API and also lets you pre-filter your data in the UI. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[v-yulgu-msft]

Hi @anktaggrwl,

Perhaps you could first expand the RecordType field, then, apply filters to [Business_Unit_c] and [Name] with OR logic.

After expanding all necessary columns, you can filter rows from UI.

Alternatively, you can directly input code like:

= Table.SelectRows(#"Changed Type", each [Category] = "A" or [Sales] = 52)

Best regards,
Yuliana Gu

[anktaggrwl]

While I know I could expand the field and then apply filters in the UI, I'm ideally looking to pass the filter in the initial call, as there are limitations in the number of rows I can pull back - so it would be more ideal to expand the RecordType field and filter for that AND filter in the business unit in one line... I'm just not sure how to write the expand and filter lines into one command.

[v-yulgu-msft]

Hi @anktaggrwl,

Please see whether below thread helpful to you:

Filtering a Power Query Table Based on a Column That's a Table

By the way, for more professional advice about Power Query, you could post questions on Power Query forum.

Best regards,
Yuliana Gu