If function parameter is null then skip step

[Anonymous]

Hi guys, 

I have some m-code which looks like the below...

The LegalEntity function parameter is optional. If this parameter is not filled in when the function is invoked (i.e. it is null), I would like PQ to basically 'skip' the "// Filter by legal entity (DataAreaId)" step and not filter the table. If the parameter is filled in (i.e. not null) I would like the table to be filtered. Any ideas?

Thank you in advance!! 🙂

(SourceTable as table, TableName as text, optional LegalEntity as text)=>

let

// Set variables
Source = SourceTable,

// Change headers to uppercase
UppercaseHeaders = Table.TransformColumnNames(Source,Text.Upper),

// Filter by ModifiedDateTime
#"Change ModifiedDateTime to Date type" = Table.TransformColumnTypes(UppercaseHeaders,{{"MODIFIEDDATETIME", type date}}),
#"Filter by ModifiedDateTime" = Table.SelectRows(#"Change ModifiedDateTime to Date type", each [MODIFIEDDATETIME] > (Date.AddDays(Date.From(DateTime.LocalNow()),-NumOfDaysSinceLastDataRefresh))),

// Filter by legal entity (DataAreaId)
#"Filter by legal entity" = each if LegalEntity = null then #"Filter by ModifiedDateTime" else (Table.SelectRows(#"Filter by ModifiedDateTime", each [DATAAREAID] = LegalEntity)),

{...more code....}

[Anonymous · Accepted solution]

Hi all, 

I have managed to get this working...

(SourceTable as table, TableName as text, optional LegalEntity as text)=>

let

// Set variables
Source = SourceTable,

// Change headers to uppercase
UppercaseHeaders = Table.TransformColumnNames(Source,Text.Upper),

// Filter by ModifiedDateTime
#"Change ModifiedDateTime to Date type" = Table.TransformColumnTypes(UppercaseHeaders,{{"MODIFIEDDATETIME", type date}}),
#"Filter by ModifiedDateTime" = Table.SelectRows(#"Change ModifiedDateTime to Date type", each [MODIFIEDDATETIME] > (Date.AddDays(Date.From(DateTime.LocalNow()),-NumOfDaysSinceLastDataRefresh))),

// If LegalEntity is not null then filter by legal entity (DataAreaId)
#"Filter by legal entity" = if LegalEntity <> null then Table.SelectRows(#"Filter by ModifiedDateTime", each [DATAAREAID] = LegalEntity) else UppercaseHeaders,