Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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....}
Solved! Go to 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,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.