cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JamesWinfield
Frequent Visitor

If function parameter is null then skip step

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....}

1 ACCEPTED SOLUTION

Accepted Solutions
JamesWinfield
Frequent Visitor

Re: If function parameter is null then skip step

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,

View solution in original post

1 REPLY 1
JamesWinfield
Frequent Visitor

Re: If function parameter is null then skip step

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,

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors